TM1 PAW - Exploration view - Insert calcul on 2 dimensions

mrdauduong
Posts: 52
Joined: Mon Sep 26, 2022 11:10 am
OLAP Product: Planning analytics
Version: PA 2.0.74.23
Excel Version: Excel 2016

TM1 PAW - Exploration view - Insert calcul on 2 dimensions

Post by mrdauduong »

hi everyone,

my client want to convert all Perspective report to Exploration view,

they have actually 2 problems that make them can not do it and stay to Perspective report:

Question 1 : they have 2 dimensions YEAR and VERSION and they want to make a calcul for exemple : VERSION A YEAR 2022 - VERSION B YEAR 2021
==> is there any way to do it on exploration view? (when i try to insert calcul, i can only chose 1 dimension)

Question 2 : then i try another way, i tried to "insert user row/colum" (function only available on PAX) and put a perso excel formula on it, it work well, but my colum inserted is always on the left of element, and i can not move to the right or the end of table. and when i try to rename the colum, it show i can not rename the colum.

If someone could advise to me how bypass this, I would appreciate.

Regards,

Cross-posted in IBM Community

https://community.ibm.com/community/use ... 4eda43146b


update 10102022 from IBM community
update from IBM community

Another way to do it and it worked from 2.0.62 version

Just a small addition to Andrew's answer.
Asymetric calculations are now supported in Exploration in Workspace, so the calculation between the two columns in you view can be done by the calculation options menu :
Reference in the documentation for classic views : https://www.ibm.com/docs/en/planning-an ... assic-view

Regards,

------------------------------
Florence Mougnaud
------------------------------
Attachments
question 2.png
question 2.png (184.67 KiB) Viewed 2908 times
question 1.png
question 1.png (723.87 KiB) Viewed 2908 times
Last edited by mrdauduong on Mon Oct 10, 2022 10:05 am, edited 2 times in total.
MarenC
Regular Participant
Posts: 346
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: TM1 PAW - Exploration view - Insert calcul on 2 dimensions

Post by MarenC »

Hi,

Unless someone can figure out a way to manipulate the MDX within the PAX exploration view to move or rename the column then I would the only way would be to use a Custom or Dynamic report in PAX to achieve this.

I would also advise the client that converting everything to an exploration kinda takes away some of the functionality of the product!

Maren
Adam
Posts: 94
Joined: Wed Apr 03, 2019 12:10 am
OLAP Product: IBM PA
Version: 2.0.9.x
Excel Version: Microsoft 365 x64

Re: TM1 PAW - Exploration view - Insert calcul on 2 dimensions

Post by Adam »

mrdauduong wrote: Thu Sep 29, 2022 7:17 am Question 1 : they have 2 dimensions YEAR and VERSION and they want to make a calcul for exemple : VERSION A YEAR 2022 - VERSION B YEAR 2021
==> is there any way to do it on exploration view? (when i try to insert calcul, i can only chose 1 dimension)
The MDX engine in TM1 is very powerful, so doing calculations like you describe is quite simple in an Exploration. Hard to ever get something like this in a GUI however, that's why you must resort to editing MDX. Edit the MDX of the report and create something like:

Code: Select all

WITH MEMBER [measure].[measure].[VERSION A YEAR 2022 - VERSION B YEAR 2021] AS 
(
  ( [version].[version].[VERSION A], [year].[year].[2022] ) 
- 
  ( [version].[version].[VERSION B], [year].[year].[2021] )
), FORMAT_STRING = '#,##0;(#,##0);-'
...
SELECT
[measure].[measure].[VERSION A YEAR 2022 - VERSION B YEAR 2021] ON 0 
...
In the above, you'll need to substitute [measure] with your measure dimension, [version] with your version dimension, [year] with your year dimension. I added the extra space for clarity, not necessary.


mrdauduong wrote: Thu Sep 29, 2022 7:17 am Question 2 : then i try another way, i tried to "insert user row/colum" (function only available on PAX) and put a perso excel formula on it, it work well, but my colum inserted is always on the left of element, and i can not move to the right or the end of table. and when i try to rename the colum, it show i can not rename the colum.

When you insert column in this way then go to edit MDX, you'll see it created a fake element, much in what you have to do for question 1.

Code: Select all


WITH MEMBER [random] AS (0) 
...
SELECT ... [random] ... ON 0
In MDX, you can rename the random element in WITH MEMBER and ON 0 section, and you can move the random element in the ON 0 section and get precisely what you're looking for.

Please let us know how you make out.
Last edited by Adam on Fri Sep 30, 2022 10:37 am, edited 1 time in total.
Take care.
Adam
MarenC
Regular Participant
Posts: 346
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: TM1 PAW - Exploration view - Insert calcul on 2 dimensions

Post by MarenC »

Hi Adam,

I presume you are talking about PAW?

If so, I would be interested to know how you would avoid the calculation appearing twice in the view if there are 2 dimensions at play?

Maren
declanr
MVP
Posts: 1815
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: TM1 PAW - Exploration view - Insert calcul on 2 dimensions

Post by declanr »

MarenC wrote: Fri Sep 30, 2022 10:32 am I presume you are talking about PAW?
If so, I would be interested to know how you would avoid the calculation appearing twice in the view if there are 2 dimensions at play?
MDX Views can be used in PAW and in PaFE explorations. For Explorations in PaFE you get the "Exploration" subsection in the ribbon which contains an "MDX" option. This will show you the autogenerated MDX, but you can just also manually alter the MDX in here to update the exploration.

To have asynchronous selections of nested elements you can use something like:

Code: Select all

Select
	{
		{[Dim1].[Hier1].[ElementA]}*{[Dim2].[Hier2].[ElementX],[Dim2].[Hier2].[ElementY]}, 
		{[Dim1].[Hier1].[ElementB]}*{[Dim2].[Hier2].[ElementX],[Dim2].[Hier2].[ElementY],[Dim2].[Hier2].[ElementZ]}, 
	} ON 0
...	
The above would nest 2 dimensions on the Columns.
It has 2 elements (A & B) from Dimension1.
For Element A, it has 2 elements (X & Y) nested from Dimension2.
But for Element B, it has 3 elements ( X, Y & Z) nested from Dimension2.
Declan Rodger
MarenC
Regular Participant
Posts: 346
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: TM1 PAW - Exploration view - Insert calcul on 2 dimensions

Post by MarenC »

Hi Declan,

I was aware that MDX could be updated in both PAW and PAX, just wasn't sure how to achieve the asynchronous selections.

But after your explanation I was able to successfully create a cube view with a calculation for Version A Year 2020/21 - Version B Year 2021/2022, and with the calculated element only appearing once in the view.

Thanks very muchly for that :D

Maren
mrdauduong
Posts: 52
Joined: Mon Sep 26, 2022 11:10 am
OLAP Product: Planning analytics
Version: PA 2.0.74.23
Excel Version: Excel 2016

Re: TM1 PAW - Exploration view - Insert calcul on 2 dimensions

Post by mrdauduong »

thank a lot, it work perfectly
Adam wrote: Thu Sep 29, 2022 7:20 pm
mrdauduong wrote: Thu Sep 29, 2022 7:17 am Question 1 : they have 2 dimensions YEAR and VERSION and they want to make a calcul for exemple : VERSION A YEAR 2022 - VERSION B YEAR 2021
==> is there any way to do it on exploration view? (when i try to insert calcul, i can only chose 1 dimension)
The MDX engine in TM1 is very powerful, so doing calculations like you describe is quite simple in an Exploration. Hard to ever get something like this in a GUI however, that's why you must resort to editing MDX. Edit the MDX of the report and create something like:

Code: Select all

WITH MEMBER [measure].[measure].[VERSION A YEAR 2022 - VERSION B YEAR 2021] AS 
(
  ( [version].[version].[VERSION A], [year].[year].[2022] ) 
- 
  ( [version].[version].[VERSION B], [year].[year].[2021] )
), FORMAT_STRING = '#,##0;(#,##0);-'
...
SELECT
[measure].[measure].[VERSION A YEAR 2022 - VERSION B YEAR 2021] ON 0 
...
In the above, you'll need to substitute [measure] with your measure dimension, [version] with your version dimension, [year] with your year dimension. I added the extra space for clarity, not necessary.


mrdauduong wrote: Thu Sep 29, 2022 7:17 am Question 2 : then i try another way, i tried to "insert user row/colum" (function only available on PAX) and put a perso excel formula on it, it work well, but my colum inserted is always on the left of element, and i can not move to the right or the end of table. and when i try to rename the colum, it show i can not rename the colum.

When you insert column in this way then go to edit MDX, you'll see it created a fake element, much in what you have to do for question 1.

Code: Select all


WITH MEMBER [random] AS (0) 
...
SELECT ... [random] ... ON 0
In MDX, you can rename the random element in WITH MEMBER and ON 0 section, and you can move the random element in the ON 0 section and get precisely what you're looking for.

Please let us know how you make out.
mrdauduong
Posts: 52
Joined: Mon Sep 26, 2022 11:10 am
OLAP Product: Planning analytics
Version: PA 2.0.74.23
Excel Version: Excel 2016

Re: TM1 PAW - Exploration view - Insert calcul on 2 dimensions

Post by mrdauduong »

thank adam,

finally, it work very well, but i have another question :mrgreen:

- is there anyway to make it dynamique by the chose of client? => for exemple, i dont want to compare version A and B but i want to compare version A and C

- is there anyway to remember the last chose of client? ==> for exemple, last time, i change my FG_Company (dimension in contexte) to another company, i want next time i go this report, it take the same company that i change last time. how i can do it?

- is turbo integrator can update an MDX view exploration?

thank a lot for your help.

Adam wrote: Thu Sep 29, 2022 7:20 pm
mrdauduong wrote: Thu Sep 29, 2022 7:17 am Question 1 : they have 2 dimensions YEAR and VERSION and they want to make a calcul for exemple : VERSION A YEAR 2022 - VERSION B YEAR 2021
==> is there any way to do it on exploration view? (when i try to insert calcul, i can only chose 1 dimension)
The MDX engine in TM1 is very powerful, so doing calculations like you describe is quite simple in an Exploration. Hard to ever get something like this in a GUI however, that's why you must resort to editing MDX. Edit the MDX of the report and create something like:

Code: Select all

WITH MEMBER [measure].[measure].[VERSION A YEAR 2022 - VERSION B YEAR 2021] AS 
(
  ( [version].[version].[VERSION A], [year].[year].[2022] ) 
- 
  ( [version].[version].[VERSION B], [year].[year].[2021] )
), FORMAT_STRING = '#,##0;(#,##0);-'
...
SELECT
[measure].[measure].[VERSION A YEAR 2022 - VERSION B YEAR 2021] ON 0 
...
In the above, you'll need to substitute [measure] with your measure dimension, [version] with your version dimension, [year] with your year dimension. I added the extra space for clarity, not necessary.


mrdauduong wrote: Thu Sep 29, 2022 7:17 am Question 2 : then i try another way, i tried to "insert user row/colum" (function only available on PAX) and put a perso excel formula on it, it work well, but my colum inserted is always on the left of element, and i can not move to the right or the end of table. and when i try to rename the colum, it show i can not rename the colum.

When you insert column in this way then go to edit MDX, you'll see it created a fake element, much in what you have to do for question 1.

Code: Select all


WITH MEMBER [random] AS (0) 
...
SELECT ... [random] ... ON 0
In MDX, you can rename the random element in WITH MEMBER and ON 0 section, and you can move the random element in the ON 0 section and get precisely what you're looking for.

Please let us know how you make out.
MarenC
Regular Participant
Posts: 346
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: TM1 PAW - Exploration view - Insert calcul on 2 dimensions

Post by MarenC »

Hi,
is there anyway to make it dynamique by the chose of client? => for exemple, i dont want to compare version A and B but i want to compare version A and C
One way would be to create a couple of attributes against the client dimension, say Client version 1 and Client version 2, and then in the MDX reference the cube, and for the client dimension the logged on client. Your dimension selectors would then have to be a picklist from the element attributes cube. Or you could create a cube which includes the client dimension and reference that in the MDX.

Maren
mrdauduong
Posts: 52
Joined: Mon Sep 26, 2022 11:10 am
OLAP Product: Planning analytics
Version: PA 2.0.74.23
Excel Version: Excel 2016

Re: TM1 PAW - Exploration view - Insert calcul on 2 dimensions

Post by mrdauduong »

MarenC wrote: Mon Oct 03, 2022 9:27 am Hi,
is there anyway to make it dynamique by the chose of client? => for exemple, i dont want to compare version A and B but i want to compare version A and C
One way would be to create a couple of attributes against the client dimension, say Client version 1 and Client version 2, and then in the MDX reference the cube, and for the client dimension the logged on client. Your dimension selectors would then have to be a picklist from the element attributes cube. Or you could create a cube which includes the client dimension and reference that in the MDX.

Maren
thanks for your reponse, can you give me 1 exemple of MDX that we can includes others dynamique variables

:idea:
MarenC
Regular Participant
Posts: 346
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: TM1 PAW - Exploration view - Insert calcul on 2 dimensions

Post by MarenC »

Hi,

example mdx that you can insert into your statement:

Code: Select all

STRTOMEMBER('[Version].[Version].[' + [}ElementAttributes_}Clients].(STRTOMEMBER("[}Clients].[}Clients].[" + USERNAME + "]"), [}ElementAttributes_}Clients].[Client Version 1]) + ']')
You would have the equivalent for Client Version 2

You would include in your with member as statement and in the select on the columns. E.g.

Code: Select all

WITH MEMBER [measure].[measure].[VERSION A YEAR 2022 - VERSION B YEAR 2021] AS 
(
  ( STRTOMEMBER('[Version].[Version].[' + [}ElementAttributes_}Clients].(STRTOMEMBER("[}Clients].[}Clients].[" + USERNAME + "]"), [}ElementAttributes_}Clients].[Client Version 1]) + ']'), [year].[year].[2022] ) 
- 
  ( STRTOMEMBER('[Version].[Version].[' + [}ElementAttributes_}Clients].(STRTOMEMBER("[}Clients].[}Clients].[" + USERNAME + "]"), [}ElementAttributes_}Clients].[Client Version 2]) + ']'), [year].[year].[2021] )
)
Maren
mrdauduong
Posts: 52
Joined: Mon Sep 26, 2022 11:10 am
OLAP Product: Planning analytics
Version: PA 2.0.74.23
Excel Version: Excel 2016

Re: TM1 PAW - Exploration view - Insert calcul on 2 dimensions

Post by mrdauduong »

thank a lot, i will try it 8-)
MarenC wrote: Mon Oct 03, 2022 4:57 pm Hi,

example mdx that you can insert into your statement:

Code: Select all

STRTOMEMBER('[Version].[Version].[' + [}ElementAttributes_}Clients].(STRTOMEMBER("[}Clients].[}Clients].[" + USERNAME + "]"), [}ElementAttributes_}Clients].[Client Version 1]) + ']')
You would have the equivalent for Client Version 2

You would include in your with member as statement and in the select on the columns. E.g.

Code: Select all

WITH MEMBER [measure].[measure].[VERSION A YEAR 2022 - VERSION B YEAR 2021] AS 
(
  ( STRTOMEMBER('[Version].[Version].[' + [}ElementAttributes_}Clients].(STRTOMEMBER("[}Clients].[}Clients].[" + USERNAME + "]"), [}ElementAttributes_}Clients].[Client Version 1]) + ']'), [year].[year].[2022] ) 
- 
  ( STRTOMEMBER('[Version].[Version].[' + [}ElementAttributes_}Clients].(STRTOMEMBER("[}Clients].[}Clients].[" + USERNAME + "]"), [}ElementAttributes_}Clients].[Client Version 2]) + ']'), [year].[year].[2021] )
)
Maren
mrdauduong
Posts: 52
Joined: Mon Sep 26, 2022 11:10 am
OLAP Product: Planning analytics
Version: PA 2.0.74.23
Excel Version: Excel 2016

Re: TM1 PAW - Exploration view - Insert calcul on 2 dimensions

Post by mrdauduong »

update from IBM community

Another way to do it and it worked from 2.0.62 version

Just a small addition to Andrew's answer.
Asymetric calculations are now supported in Exploration in Workspace, so the calculation between the two columns in you view can be done by the calculation options menu :
Reference in the documentation for classic views : https://www.ibm.com/docs/en/planning-an ... assic-view

Regards,

------------------------------
Florence Mougnaud
------------------------------
MarenC
Regular Participant
Posts: 346
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: TM1 PAW - Exploration view - Insert calcul on 2 dimensions

Post by MarenC »

Hi,

So you were able to do the following calculation:

VERSION A YEAR 2022 - VERSION B YEAR 2021

by using the out of the box calculation options in PAW?

Can you please explain step by step how you did that using this method?

Maren
mrdauduong
Posts: 52
Joined: Mon Sep 26, 2022 11:10 am
OLAP Product: Planning analytics
Version: PA 2.0.74.23
Excel Version: Excel 2016

Re: TM1 PAW - Exploration view - Insert calcul on 2 dimensions

Post by mrdauduong »

hi Maren,
yes, iam able to do this calcul VERSION A YEAR 2022 - VERSION B YEAR 2021

i follow the produre of this link https://www.ibm.com/docs/en/planning-an ... assic-view

- Click the Targeted selection icon for the axis where you want to create the asymmetric calculation. Targeted selection icon for rows for rows and Targeted selection ison for columns for columns.
- Select the members you want to be included in the calculation. Use CTRL+click to select multiple non-adjacent members, use SHIFT+click to select multiple adjacent members. The asymmetric calculation will be inserted adjacent to the last member you select.
- Right-click a selected member, then click Create calculation.
- Select the calculation you want to apply.
MarenC wrote: Mon Oct 10, 2022 10:39 am Hi,

So you were able to do the following calculation:

VERSION A YEAR 2022 - VERSION B YEAR 2021

by using the out of the box calculation options in PAW?

Can you please explain step by step how you did that using this method?

Maren
MarenC
Regular Participant
Posts: 346
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: TM1 PAW - Exploration view - Insert calcul on 2 dimensions

Post by MarenC »

Hi,

So when you Ctrl click to select the target members you are doing the following:

Ctrl click VERSION A, Ctrl Click Year 2022, Ctrl Click VERSION B, Ctrl Click Year 2021?

You then right click and create calculation and one of the options that comes up is VERSION A YEAR 2022 - VERSION B YEAR 2021?

I would be really interested to see the screenshots for that!

Maren
mrdauduong
Posts: 52
Joined: Mon Sep 26, 2022 11:10 am
OLAP Product: Planning analytics
Version: PA 2.0.74.23
Excel Version: Excel 2016

Re: TM1 PAW - Exploration view - Insert calcul on 2 dimensions

Post by mrdauduong »

options that comes up is VERSION A YEAR 2022 - VERSION B YEAR 2021? ==> the option is always 2022-2021 but the result is different. only VERSION A YEAR 2022 - VERSION B YEAR 2021 for all version

MarenC wrote: Mon Oct 10, 2022 2:25 pm Hi,

So when you Ctrl click to select the target members you are doing the following:

Ctrl click VERSION A, Ctrl Click Year 2022, Ctrl Click VERSION B, Ctrl Click Year 2021?

You then right click and create calculation and one of the options that comes up is VERSION A YEAR 2022 - VERSION B YEAR 2021?

I would be really interested to see the screenshots for that!

Maren
Attachments
photo 1.png
photo 1.png (52.74 KiB) Viewed 2510 times
photo 2.PNG
photo 2.PNG (21.83 KiB) Viewed 2511 times
MarenC
Regular Participant
Posts: 346
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: TM1 PAW - Exploration view - Insert calcul on 2 dimensions

Post by MarenC »

Hi,

So in other words, the calculation method is not calculating VERSION A YEAR 2022 - VERSION B YEAR 2021, but is in fact sinply calculating 2021 - 2020.

Glad we cleared that up!

Maren
Keith Would
Posts: 13
Joined: Tue Dec 06, 2016 11:24 am
OLAP Product: TM1
Version: 10.3.0
Excel Version: 2010

Re: TM1 PAW - Exploration view - Insert calcul on 2 dimensions

Post by Keith Would »

It does work, that probably wasn't clear from the screenshot or the explanation.

When the arithmetic options come up, they only show one element (in my example 825 and 269) not all of the selection. Hence why in the above it just shows 2021 - 2020.

Image
MarenC
Regular Participant
Posts: 346
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: TM1 PAW - Exploration view - Insert calcul on 2 dimensions

Post by MarenC »

Hi Keith,

Edited:

I get what you mean, you just select from one dimension but from different blocks of the other dimension. Well, I never knew that worked.

I should thank Mr Duong for his persistence! Though I should add if he wants to makes this dynamic(que) then I presume the MDX option is the only way.


Maren
Post Reply