Planning Analytics

 View Only

 PAW MDX Views

Simon Mehnert's profile image
Simon Mehnert posted Mon February 17, 2025 04:43 PM

Hi All...

I have a view with 2 versions being compared, and then a calulated field in the view;

As you all probably know, when I change the Version comparitors I loose the 'Variance' calculation.

So following a thread from Decland - From Spitfire, I added a cube of users selections onto the PAW report.

When I try and reference the cube value the comparator disappears, what am I doing wrong?... here is the MDX;

WITH 
   MEMBER [Version].[Version].[Active Forecasts].[Variance] AS [Version].[Version].[PPC25 Version 2]/[Version].[Version].[Forecast], SOLVE_ORDER = 1, FORMAT_STRING = '#,##0.00;(#,##0.00)' 
   MEMBER [Student FTE Planning Measures].[Student FTE Planning Measures].[Header_#000001739438821487#] AS "", SOLVE_ORDER = 2147483646 
SELECT 
   {
      [Student FTE Planning Measures].[Student FTE Planning Measures].[Continuation Yr1 (New Intake)], 
      [Student FTE Planning Measures].[Student FTE Planning Measures].[Continuation Yr2], 
      [Student FTE Planning Measures].[Student FTE Planning Measures].[Continuation Yr3], 
      [Student FTE Planning Measures].[Student FTE Planning Measures].[Header_#000001739438821487#], 
      [Student FTE Planning Measures].[Student FTE Planning Measures].[Continuation Yr1-Yr2], 
      [Student FTE Planning Measures].[Student FTE Planning Measures].[Continuation Yr2-Yr3]
   } ON 0, NON EMPTY 
   {
      [Version].[Version].[Forecast],
       STRTOMEMBER("[Version].[Version].[" + [UserSelections].([}Clients].[CAMID("pans:u:mehnert@liverpool.ac.uk")],[UserSelection_Measures].[VS_To]) + "]"), 
      [Version].[Version].[Variance]
   }*{
      TM1SubsetToSet([Academic Year].[Academic Year],"CurrentPlanyears","public")
   } ON 1 
FROM
   [Student FTE Planning] 
WHERE (
   [Snapshot].[Snapshot].[Yearly Average], 
   [Intake Flag].[Intake Flag].[Current], 
   [Headcount Type].[Headcount Type].[Total Headcount Type], 
   [Fee Regime].[Fee Regime].[ALL REGIME], 
   [Registration Department].[Registration Department].[SCE], 
   [Year of Programme].[Year of Programme].[1], 
   [Campus].[Campus].[ALL CAMPUSES], 
   [Student Characteristic].[Student Characteristic].[Standard], 
   [Mode].[Mode].[ALL MODE], 
   [Residence].[Residence].[ALL RESIDENCE], 
   [Programme].[Programme].[ALL PROGRAMMES], 
   [Teaching Department].[Teaching Department].[The University of Liverpool], 
   [Level of Study].[Level of Study].[UG])

My alteration was in bold above the view accepts the mdx change, but then its not displayed!!!

Please advise

Andre Negrini Turina's profile image
Andre Negrini Turina posted Wed February 19, 2025 07:28 AM

Hi Simon,

You can try add the same logic STRTOMEMBER on the top when you define the member. Something like:

WITH 
   MEMBER [Version].[Version].[Active Forecasts].[Variance] AS 
   
    STRTOMEMBER("[Version].[Version].[" + [UserSelections].([}Clients].[CAMID("pans:u:mehnert@liverpool.ac.uk")],[UserSelection_Measures].[VS_To]) + "]")
   /
   [Version].[Version].[Forecast],
   ...

Hope this can help :)

Andre Negrini Turina's profile image
Andre Negrini Turina posted Wed February 19, 2025 07:45 AM

Hi Simon,

You can use the STRTOMEMBER on the top when you define the calculated MEMBER. Something like:

WITH 
   MEMBER [Version].[Version].[Active Forecasts].[Variance] AS 
   
    STRTOMEMBER("[Version].[Version].[" + [UserSelections].([}Clients].[CAMID("pans:u:mehnert@liverpool.ac.uk")],[UserSelection_Measures].[VS_To]) + "]")
   /
   [Version].[Version].[Forecast],
   ...

Hope this can help :)

Johann Kassier's profile image
Johann Kassier IBM Champion posted Thu February 20, 2025 07:42 AM

Hi Simon
Can I suggest trying the following MDX in the place of your STRTOMEMBER bit:
STRTOMEMBER("[Version].[Version].[" + [UserSelections].(STRTOMEMBER("[}Clients].[" + UserName + "]"),[UserSelection_Measures].[VS_To]) + "]")
This will dynamically derive the user name based on the active user. It also avoids the additional double quote and potential clashes in the code vs the name of your user.

Secondly, you can also update your WITH MEMBER definition of variance to calculate the variance between Forecast and the user selected version by including this same STRTOMEMBER logic in the WITH MEMBER definition. 

Hope that helps!

Simon Mehnert's profile image
Simon Mehnert posted Sun February 23, 2025 04:02 PM

@Johann Kassier Thank you for your input... I managed to get the calulcation to work as shown above... However, in the rows the calculated row does not persist... As highlighted in the above in bold, I can run the mdx view save it.  I close the report and go back in and the mdx calulculated row is now a hardcoded element value, and is therefore no longer dynamic!

George Tonkin's profile image
George Tonkin IBM Champion posted Mon February 24, 2025 01:04 AM

Have a look at this thread on the TM1Forum where a similar issues is being discussed and a similar, what appears to be a bug, hampering a simple solution. Some previous posts here have also mentioned code being changed or views becoming static and set changes not updating.