TM1 PAW - Exploration view - Insert calcul on 2 dimensions

[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
------------------------------

[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]

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.

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]

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]

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.

[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

Maren

[mrdauduong]

thank a lot, it work perfectly

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.

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]

thank adam,

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

- 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.

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.

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]

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]

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

[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]

thank a lot, i will try it

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]

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]

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]

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.

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]

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]

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

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

[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]

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.

[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