Planning Analytics

Hi Duong, see my thoughts on your two questions below.

Q1: As far as I know, the insert calc option in pax/paw is for basic calcs across one dimension only. Looking at your first screenshot it looks like the middle column dim with elements Assu Prev 2-2021 and Assu Budget 2022 is a version dimension. Forgetting pax/paw for a minute, you can likely achieve the desired comparison with a bit of dimension and data work. You could create a new version simply called Assu Prev Year and then copy all data from Version:Assu Prev 2-21 & Year:2021 into that new version and year 2022. At this point, you'll have commonality in the year dimension so can do the simple comparison in PAW with the insert calc. You could also create a consolidated element in the version dim that takes the difference between the Assu Budget 2022 version and your newly created Assu Prev Year version. The consolidation would likely be much more efficient than the MDX generated calc and would have the added benefit of being readily-accessible in any view/exploration.

Q2: I'm not aware of an easy way to do this, but it is possible.  The first way would be to add one more element to the list than you actually want and create the user column. Then go into the subset editor, select the element at the bottom of the list which should be the unnecessary one you added and delete it from the list. The second way would be modify the MDX in subset editor after adding the user column. Below is an example of original MDX with user column between two elements and updated MDX with user column at the end.

Original MDX:

GENERATE(    {[Time].[Time].[Qtr 2^Jun],    [Time].[Time].[Qtr 3^Jul]} , STRTOSET( CASE                   WHEN 0 < INSTR([Time].[Time].CURRENTMEMBER.UNIQUENAME , "[Qtr 3^Jul]")                 THEN "{[Time].[Time].[Header_#638006833118578110#]," + [Time].[Time].CURRENTMEMBER.UNIQUENAME + "}"                  ELSE "{ " + [Time].[Time].CURRENTMEMBER.UNIQUENAME + " }"             END ) , ALL)

Updated MDX:

GENERATE(    {[Time].[Time].[Qtr 2^Jun],    [Time].[Time].[Qtr 3^Jul]} , STRTOSET( CASE                   WHEN 0 < INSTR([Time].[Time].CURRENTMEMBER.UNIQUENAME , "[Qtr 3^Jul]")                 THEN "{" + [Time].[Time].CURRENTMEMBER.UNIQUENAME + ", [Time].[Time].[Header_#638006833118578110#]}"                ELSE "{ " + [Time].[Time].CURRENTMEMBER.UNIQUENAME + " }"             END ) , ALL)

Hi Duong, see my thoughts on your two questions below.

Q1: As far as I know, the insert calc option in pax/paw is for basic calcs across one dimension only. Looking at your first screenshot it looks like the middle column dim with elements Assu Prev 2-2021 and Assu Budget 2022 is a version dimension. Forgetting pax/paw for a minute, you can likely achieve the desired comparison with a bit of dimension and data work. You could create a new version simply called Assu Prev Year and then copy all data from Version:Assu Prev 2-21 & Year:2021 into that new version and year 2022. At this point, you'll have commonality in the year dimension so can do the simple comparison in PAW with the insert calc. You could also create a consolidated element in the version dim that takes the difference between the Assu Budget 2022 version and your newly created Assu Prev Year version. The consolidation would likely be much more efficient than the MDX generated calc and would have the added benefit of being readily-accessible in any view/exploration.

Q2: I'm not aware of an easy way to do this, but it is possible.  The first way would be to add one more element to the list than you actually want and create the user column. Then go into the subset editor, select the element at the bottom of the list which should be the unnecessary one you added and delete it from the list. The second way would be modify the MDX in subset editor after adding the user column. Below is an example of original MDX with user column between two elements and updated MDX with user column at the end.

Original MDX:

GENERATE(    {[Time].[Time].[Qtr 2^Jun],    [Time].[Time].[Qtr 3^Jul]} , STRTOSET( CASE                   WHEN 0 < INSTR([Time].[Time].CURRENTMEMBER.UNIQUENAME , "[Qtr 3^Jul]")                 THEN "{[Time].[Time].[Header_#638006833118578110#]," + [Time].[Time].CURRENTMEMBER.UNIQUENAME + "}"                  ELSE "{ " + [Time].[Time].CURRENTMEMBER.UNIQUENAME + " }"             END ) , ALL)

Updated MDX:

GENERATE(    {[Time].[Time].[Qtr 2^Jun],    [Time].[Time].[Qtr 3^Jul]} , STRTOSET( CASE                   WHEN 0 < INSTR([Time].[Time].CURRENTMEMBER.UNIQUENAME , "[Qtr 3^Jul]")                 THEN "{" + [Time].[Time].CURRENTMEMBER.UNIQUENAME + ", [Time].[Time].[Header_#638006833118578110#]}"                ELSE "{ " + [Time].[Time].CURRENTMEMBER.UNIQUENAME + " }"             END ) , ALL)

Hi Duong, see my thoughts on your two questions below.

Q1: As far as I know, the insert calc option in pax/paw is for basic calcs across one dimension only. Looking at your first screenshot it looks like the middle column dim with elements Assu Prev 2-2021 and Assu Budget 2022 is a version dimension. Forgetting pax/paw for a minute, you can likely achieve the desired comparison with a bit of dimension and data work. You could create a new version simply called Assu Prev Year and then copy all data from Version:Assu Prev 2-21 & Year:2021 into that new version and year 2022. At this point, you'll have commonality in the year dimension so can do the simple comparison in PAW with the insert calc. You could also create a consolidated element in the version dim that takes the difference between the Assu Budget 2022 version and your newly created Assu Prev Year version. The consolidation would likely be much more efficient than the MDX generated calc and would have the added benefit of being readily-accessible in any view/exploration.

Q2: I'm not aware of an easy way to do this, but it is possible.  The first way would be to add one more element to the list than you actually want and create the user column. Then go into the subset editor, select the element at the bottom of the list which should be the unnecessary one you added and delete it from the list. The second way would be modify the MDX in subset editor after adding the user column. Below is an example of original MDX with user column between two elements and updated MDX with user column at the end.

Original MDX:

GENERATE(    {[Time].[Time].[Qtr 2^Jun],    [Time].[Time].[Qtr 3^Jul]} , STRTOSET( CASE                   WHEN 0 < INSTR([Time].[Time].CURRENTMEMBER.UNIQUENAME , "[Qtr 3^Jul]")                 THEN "{[Time].[Time].[Header_#638006833118578110#]," + [Time].[Time].CURRENTMEMBER.UNIQUENAME + "}"                  ELSE "{ " + [Time].[Time].CURRENTMEMBER.UNIQUENAME + " }"             END ) , ALL)

Updated MDX:

GENERATE(    {[Time].[Time].[Qtr 2^Jun],    [Time].[Time].[Qtr 3^Jul]} , STRTOSET( CASE                   WHEN 0 < INSTR([Time].[Time].CURRENTMEMBER.UNIQUENAME , "[Qtr 3^Jul]")                 THEN "{" + [Time].[Time].CURRENTMEMBER.UNIQUENAME + ", [Time].[Time].[Header_#638006833118578110#]}"                ELSE "{ " + [Time].[Time].CURRENTMEMBER.UNIQUENAME + " }"             END ) , ALL)

Hi Duong, see my thoughts on your two questions below.

Q1: As far as I know, the insert calc option in pax/paw is for basic calcs across one dimension only. Looking at your first screenshot it looks like the middle column dim with elements Assu Prev 2-2021 and Assu Budget 2022 is a version dimension. Forgetting pax/paw for a minute, you can likely achieve the desired comparison with a bit of dimension and data work. You could create a new version simply called Assu Prev Year and then copy all data from Version:Assu Prev 2-21 & Year:2021 into that new version and year 2022. At this point, you'll have commonality in the year dimension so can do the simple comparison in PAW with the insert calc. You could also create a consolidated element in the version dim that takes the difference between the Assu Budget 2022 version and your newly created Assu Prev Year version. The consolidation would likely be much more efficient than the MDX generated calc and would have the added benefit of being readily-accessible in any view/exploration.

Q2: I'm not aware of an easy way to do this, but it is possible.  The first way would be to add one more element to the list than you actually want and create the user column. Then go into the subset editor, select the element at the bottom of the list which should be the unnecessary one you added and delete it from the list. The second way would be modify the MDX in subset editor after adding the user column. Below is an example of original MDX with user column between two elements and updated MDX with user column at the end.

Original MDX:

GENERATE(    {[Time].[Time].[Qtr 2^Jun],    [Time].[Time].[Qtr 3^Jul]} , STRTOSET( CASE                   WHEN 0 < INSTR([Time].[Time].CURRENTMEMBER.UNIQUENAME , "[Qtr 3^Jul]")                 THEN "{[Time].[Time].[Header_#638006833118578110#]," + [Time].[Time].CURRENTMEMBER.UNIQUENAME + "}"                  ELSE "{ " + [Time].[Time].CURRENTMEMBER.UNIQUENAME + " }"             END ) , ALL)

Updated MDX:

GENERATE(    {[Time].[Time].[Qtr 2^Jun],    [Time].[Time].[Qtr 3^Jul]} , STRTOSET( CASE                   WHEN 0 < INSTR([Time].[Time].CURRENTMEMBER.UNIQUENAME , "[Qtr 3^Jul]")                 THEN "{" + [Time].[Time].CURRENTMEMBER.UNIQUENAME + ", [Time].[Time].[Header_#638006833118578110#]}"                ELSE "{ " + [Time].[Time].CURRENTMEMBER.UNIQUENAME + " }"             END ) , ALL)

Hi Duong, see my thoughts on your two questions below.

Q1: As far as I know, the insert calc option in pax/paw is for basic calcs across one dimension only. Looking at your first screenshot it looks like the middle column dim with elements Assu Prev 2-2021 and Assu Budget 2022 is a version dimension. Forgetting pax/paw for a minute, you can likely achieve the desired comparison with a bit of dimension and data work. You could create a new version simply called Assu Prev Year and then copy all data from Version:Assu Prev 2-21 & Year:2021 into that new version and year 2022. At this point, you'll have commonality in the year dimension so can do the simple comparison in PAW with the insert calc. You could also create a consolidated element in the version dim that takes the difference between the Assu Budget 2022 version and your newly created Assu Prev Year version. The consolidation would likely be much more efficient than the MDX generated calc and would have the added benefit of being readily-accessible in any view/exploration.

Q2: I'm not aware of an easy way to do this, but it is possible.  The first way would be to add one more element to the list than you actually want and create the user column. Then go into the subset editor, select the element at the bottom of the list which should be the unnecessary one you added and delete it from the list. The second way would be modify the MDX in subset editor after adding the user column. Below is an example of original MDX with user column between two elements and updated MDX with user column at the end.

Original MDX:

GENERATE(    {[Time].[Time].[Qtr 2^Jun],    [Time].[Time].[Qtr 3^Jul]} , STRTOSET( CASE                   WHEN 0 < INSTR([Time].[Time].CURRENTMEMBER.UNIQUENAME , "[Qtr 3^Jul]")                 THEN "{[Time].[Time].[Header_#638006833118578110#]," + [Time].[Time].CURRENTMEMBER.UNIQUENAME + "}"                  ELSE "{ " + [Time].[Time].CURRENTMEMBER.UNIQUENAME + " }"             END ) , ALL)

Updated MDX:

GENERATE(    {[Time].[Time].[Qtr 2^Jun],    [Time].[Time].[Qtr 3^Jul]} , STRTOSET( CASE                   WHEN 0 < INSTR([Time].[Time].CURRENTMEMBER.UNIQUENAME , "[Qtr 3^Jul]")                 THEN "{" + [Time].[Time].CURRENTMEMBER.UNIQUENAME + ", [Time].[Time].[Header_#638006833118578110#]}"                ELSE "{ " + [Time].[Time].CURRENTMEMBER.UNIQUENAME + " }"             END ) , ALL)

Hi Duong, see my thoughts on your two questions below.

Q1: As far as I know, the insert calc option in pax/paw is for basic calcs across one dimension only. Looking at your first screenshot it looks like the middle column dim with elements Assu Prev 2-2021 and Assu Budget 2022 is a version dimension. Forgetting pax/paw for a minute, you can likely achieve the desired comparison with a bit of dimension and data work. You could create a new version simply called Assu Prev Year and then copy all data from Version:Assu Prev 2-21 & Year:2021 into that new version and year 2022. At this point, you'll have commonality in the year dimension so can do the simple comparison in PAW with the insert calc. You could also create a consolidated element in the version dim that takes the difference between the Assu Budget 2022 version and your newly created Assu Prev Year version. The consolidation would likely be much more efficient than the MDX generated calc and would have the added benefit of being readily-accessible in any view/exploration.

Q2: I'm not aware of an easy way to do this, but it is possible.  The first way would be to add one more element to the list than you actually want and create the user column. Then go into the subset editor, select the element at the bottom of the list which should be the unnecessary one you added and delete it from the list. The second way would be modify the MDX in subset editor after adding the user column. Below is an example of original MDX with user column between two elements and updated MDX with user column at the end.

Original MDX:

GENERATE(    {[Time].[Time].[Qtr 2^Jun],    [Time].[Time].[Qtr 3^Jul]} , STRTOSET( CASE                   WHEN 0 < INSTR([Time].[Time].CURRENTMEMBER.UNIQUENAME , "[Qtr 3^Jul]")                 THEN "{[Time].[Time].[Header_#638006833118578110#]," + [Time].[Time].CURRENTMEMBER.UNIQUENAME + "}"                  ELSE "{ " + [Time].[Time].CURRENTMEMBER.UNIQUENAME + " }"             END ) , ALL)

Updated MDX:

GENERATE(    {[Time].[Time].[Qtr 2^Jun],    [Time].[Time].[Qtr 3^Jul]} , STRTOSET( CASE                   WHEN 0 < INSTR([Time].[Time].CURRENTMEMBER.UNIQUENAME , "[Qtr 3^Jul]")                 THEN "{" + [Time].[Time].CURRENTMEMBER.UNIQUENAME + ", [Time].[Time].[Header_#638006833118578110#]}"                ELSE "{ " + [Time].[Time].CURRENTMEMBER.UNIQUENAME + " }"             END ) , ALL)

Hi Duong, see my thoughts on your two questions below.

Q1: As far as I know, the insert calc option in pax/paw is for basic calcs across one dimension only. Looking at your first screenshot it looks like the middle column dim with elements Assu Prev 2-2021 and Assu Budget 2022 is a version dimension. Forgetting pax/paw for a minute, you can likely achieve the desired comparison with a bit of dimension and data work. You could create a new version simply called Assu Prev Year and then copy all data from Version:Assu Prev 2-21 & Year:2021 into that new version and year 2022. At this point, you'll have commonality in the year dimension so can do the simple comparison in PAW with the insert calc. You could also create a consolidated element in the version dim that takes the difference between the Assu Budget 2022 version and your newly created Assu Prev Year version. The consolidation would likely be much more efficient than the MDX generated calc and would have the added benefit of being readily-accessible in any view/exploration.

Q2: I'm not aware of an easy way to do this, but it is possible.  The first way would be to add one more element to the list than you actually want and create the user column. Then go into the subset editor, select the element at the bottom of the list which should be the unnecessary one you added and delete it from the list. The second way would be modify the MDX in subset editor after adding the user column. Below is an example of original MDX with user column between two elements and updated MDX with user column at the end.

Original MDX:

GENERATE(    {[Time].[Time].[Qtr 2^Jun],    [Time].[Time].[Qtr 3^Jul]} , STRTOSET( CASE                   WHEN 0 < INSTR([Time].[Time].CURRENTMEMBER.UNIQUENAME , "[Qtr 3^Jul]")                 THEN "{[Time].[Time].[Header_#638006833118578110#]," + [Time].[Time].CURRENTMEMBER.UNIQUENAME + "}"                  ELSE "{ " + [Time].[Time].CURRENTMEMBER.UNIQUENAME + " }"             END ) , ALL)

Updated MDX:

GENERATE(    {[Time].[Time].[Qtr 2^Jun],    [Time].[Time].[Qtr 3^Jul]} , STRTOSET( CASE                   WHEN 0 < INSTR([Time].[Time].CURRENTMEMBER.UNIQUENAME , "[Qtr 3^Jul]")                 THEN "{" + [Time].[Time].CURRENTMEMBER.UNIQUENAME + ", [Time].[Time].[Header_#638006833118578110#]}"                ELSE "{ " + [Time].[Time].CURRENTMEMBER.UNIQUENAME + " }"             END ) , ALL)