Hello,

I need some help with creating a prompt/build elements which shows the following and the previous year for the selected in prompt year.



What | need to achieve?:
a) A report in IBM CA 11.1 with IBM PA 2.0.6 cubes as a source data
b) I need to create three (3) columns:
There is one Cube [C_Sales] with many dimensions including:
1. V_Version - contains elements [Version1], [Version2]...
2. T_Time - contains a time dimension with levels : Total,Years,Quarters, Months. Each element have addtional text attributes:
I have created two elements in Report Studio query:

I have tried to create previous year element as:
But it does not work in the tuple mentioned at the beggining.

Can you suggest any other way to achieve the expected result?

Regards,
R. Klingiert

------------------------------
Robert Klingiert
------------------------------

#CognosAnalyticswithWatson

prevMember should work, what error are you getting?

------------------------------
Paul Mendelson
------------------------------

Original Message:
Sent: Tue October 12, 2021 07:22 AM
From: Robert Klingiert
Subject: Relative time prompt with TM1 Cube

Hello,

I need some help with creating a prompt/build elements which shows the following and the previous year for the selected in prompt year.



What | need to achieve?:
a) A report in IBM CA 11.1 with IBM PA 2.0.6 cubes as a source data
b) I need to create three (3) columns:

b.1 - tuple ([Version1]; [Selected Year -1])

b.2 - tuple ([Version2]; [Selected Year])

b.3 - tuple ([Version1]; [Selected Year +1])

There is one Cube [C_Sales] with many dimensions including:
1. V_Version - contains elements [Version1], [Version2]...
2. T_Time - contains a time dimension with levels : Total,Years,Quarters, Months. Each element have addtional text attributes:

Element Caption Next_Year Prev_Year

[2021]    '2021'   '2022'         '2020'

[2022]    '2022'   '2023'         '2021'

[2023]    '2023'   '2024'         '2022'

I have created two elements in Report Studio query:

SelectedVersion = [C_Sales].[V_Version].[V_Version]->?pVersion?

SelectedYear = [C_Sales].[T_Time].[T_Time].[Years]->?pYear?

I have tried to create previous year element as:

[Selected Year -1]=PrevMember(SelectedYear);

But it does not work in the tuple mentioned at the beggining.

Can you suggest any other way to achieve the expected result?

Regards,
R. Klingiert

------------------------------
Robert Klingiert
------------------------------

#CognosAnalyticswithWatson

Unfortunately, no error messages appeared,
only the data does not appear in the pivot table

------------------------------
Robert Klingiert
------------------------------

Original Message:
Sent: Thu October 14, 2021 10:34 AM
From: Paul Mendelson
Subject: Relative time prompt with TM1 Cube

prevMember should work, what error are you getting?

------------------------------
Paul Mendelson

Original Message:
Sent: Tue October 12, 2021 07:22 AM
From: Robert Klingiert
Subject: Relative time prompt with TM1 Cube

Hello,

I need some help with creating a prompt/build elements which shows the following and the previous year for the selected in prompt year.



What | need to achieve?:
a) A report in IBM CA 11.1 with IBM PA 2.0.6 cubes as a source data
b) I need to create three (3) columns:

b.1 - tuple ([Version1]; [Selected Year -1])

b.2 - tuple ([Version2]; [Selected Year])

b.3 - tuple ([Version1]; [Selected Year +1])

There is one Cube [C_Sales] with many dimensions including:
1. V_Version - contains elements [Version1], [Version2]...
2. T_Time - contains a time dimension with levels : Total,Years,Quarters, Months. Each element have addtional text attributes:

Element Caption Next_Year Prev_Year

[2021]    '2021'   '2022'         '2020'

[2022]    '2022'   '2023'         '2021'

[2023]    '2023'   '2024'         '2022'

I have created two elements in Report Studio query:

SelectedVersion = [C_Sales].[V_Version].[V_Version]->?pVersion?

SelectedYear = [C_Sales].[T_Time].[T_Time].[Years]->?pYear?

I have tried to create previous year element as:

[Selected Year -1]=PrevMember(SelectedYear);

But it does not work in the tuple mentioned at the beggining.

Can you suggest any other way to achieve the expected result?

Regards,
R. Klingiert

------------------------------
Robert Klingiert
------------------------------

#CognosAnalyticswithWatson

In this case I recommend testing each item step by step.

First, do you have any detail filters in your query? If so I recommend removing them, they may cause unexpected results. 

Create a new crosstab and put Selected Year-1, Selected Year, Selected Year+1 in the rows as peers. This is to make sure those data items are actually returning the expected members. 

Next replace those with the tuples. If that works then it might be something you have on a different edge.

------------------------------
Paul Mendelson
------------------------------

Original Message:
Sent: Tue October 19, 2021 04:25 AM
From: Robert Klingiert
Subject: Relative time prompt with TM1 Cube

Unfortunately, no error messages appeared,
only the data does not appear in the pivot table

------------------------------
Robert Klingiert

Original Message:
Sent: Thu October 14, 2021 10:34 AM
From: Paul Mendelson
Subject: Relative time prompt with TM1 Cube

prevMember should work, what error are you getting?

------------------------------
Paul Mendelson

Original Message:
Sent: Tue October 12, 2021 07:22 AM
From: Robert Klingiert
Subject: Relative time prompt with TM1 Cube

Hello,

I need some help with creating a prompt/build elements which shows the following and the previous year for the selected in prompt year.



What | need to achieve?:
a) A report in IBM CA 11.1 with IBM PA 2.0.6 cubes as a source data
b) I need to create three (3) columns:

b.1 - tuple ([Version1]; [Selected Year -1])

b.2 - tuple ([Version2]; [Selected Year])

b.3 - tuple ([Version1]; [Selected Year +1])

There is one Cube [C_Sales] with many dimensions including:
1. V_Version - contains elements [Version1], [Version2]...
2. T_Time - contains a time dimension with levels : Total,Years,Quarters, Months. Each element have addtional text attributes:

Element Caption Next_Year Prev_Year

[2021]    '2021'   '2022'         '2020'

[2022]    '2022'   '2023'         '2021'

[2023]    '2023'   '2024'         '2022'

I have created two elements in Report Studio query:

SelectedVersion = [C_Sales].[V_Version].[V_Version]->?pVersion?

SelectedYear = [C_Sales].[T_Time].[T_Time].[Years]->?pYear?

I have tried to create previous year element as:

[Selected Year -1]=PrevMember(SelectedYear);

But it does not work in the tuple mentioned at the beggining.

Can you suggest any other way to achieve the expected result?

Regards,
R. Klingiert

------------------------------
Robert Klingiert
------------------------------

#CognosAnalyticswithWatson

Thanks a lot for the suggestion.
In the meantime, I used another solution that also works.

ie. 
Query Item name: [Selected Year -1]

item(filter([C_Sales].[T_Time].[T_Time].[Years]; [C_Sales].[T_Time].[T_Time].[Years].[Next_year]=?pYear?);0)

So on, and I have used it in tuples. That works.

------------------------------
Robert Klingiert
------------------------------

Original Message:
Sent: Wed October 20, 2021 02:56 AM
From: Paul Mendelson
Subject: Relative time prompt with TM1 Cube

In this case I recommend testing each item step by step.

First, do you have any detail filters in your query? If so I recommend removing them, they may cause unexpected results. 

Create a new crosstab and put Selected Year-1, Selected Year, Selected Year+1 in the rows as peers. This is to make sure those data items are actually returning the expected members. 

Next replace those with the tuples. If that works then it might be something you have on a different edge.

------------------------------
Paul Mendelson

Original Message:
Sent: Tue October 19, 2021 04:25 AM
From: Robert Klingiert
Subject: Relative time prompt with TM1 Cube

Unfortunately, no error messages appeared,
only the data does not appear in the pivot table

------------------------------
Robert Klingiert

Original Message:
Sent: Thu October 14, 2021 10:34 AM
From: Paul Mendelson
Subject: Relative time prompt with TM1 Cube

prevMember should work, what error are you getting?

------------------------------
Paul Mendelson

Original Message:
Sent: Tue October 12, 2021 07:22 AM
From: Robert Klingiert
Subject: Relative time prompt with TM1 Cube

Hello,

I need some help with creating a prompt/build elements which shows the following and the previous year for the selected in prompt year.



What | need to achieve?:
a) A report in IBM CA 11.1 with IBM PA 2.0.6 cubes as a source data
b) I need to create three (3) columns:

b.1 - tuple ([Version1]; [Selected Year -1])

b.2 - tuple ([Version2]; [Selected Year])

b.3 - tuple ([Version1]; [Selected Year +1])

There is one Cube [C_Sales] with many dimensions including:
1. V_Version - contains elements [Version1], [Version2]...
2. T_Time - contains a time dimension with levels : Total,Years,Quarters, Months. Each element have addtional text attributes:

Element Caption Next_Year Prev_Year

[2021]    '2021'   '2022'         '2020'

[2022]    '2022'   '2023'         '2021'

[2023]    '2023'   '2024'         '2022'

I have created two elements in Report Studio query:

SelectedVersion = [C_Sales].[V_Version].[V_Version]->?pVersion?

SelectedYear = [C_Sales].[T_Time].[T_Time].[Years]->?pYear?

I have tried to create previous year element as:

[Selected Year -1]=PrevMember(SelectedYear);

But it does not work in the tuple mentioned at the beggining.

Can you suggest any other way to achieve the expected result?

Regards,
R. Klingiert

------------------------------
Robert Klingiert
------------------------------

#CognosAnalyticswithWatson