Planning Analytics with Watson

 View Only
Expand all | Collapse all

Synchronize Calculated item between sheets in PAW

  • 1.  Synchronize Calculated item between sheets in PAW

    Posted Thu September 02, 2021 08:37 AM
    Hi All,

    I created a calculated item in a PAW sheet and want this to be synchronized to rest sheets of the book. Any change in selection of dimension elements in one sheet is reflecting correctly in other sheets except calculated item. Is there a way to sync that as well?


    Thanks and Regards
    Sumit Suman

  • 2.  RE: Synchronize Calculated item between sheets in PAW

    IBM Champion
    Posted Sat September 04, 2021 04:40 PM

    Hi Sumit,

    To the best of my knowledge this is not currently possible.

    When you create a calculation in PAW, it doesn't actually add anything to the hierarchy but it instead adds the calculation in the MDX view.
    If you look at the MDX for your view you will see it start with something like "WITH MEMBER [MyDim].[MyHierarchy].[MyCalculation] AS XYZ....

    The hierarchy synchronization to other views will then actually still include a reference to this fake "member" and you can see that in the view MDX for other views where it will be listed as part of the select statement BUT with an added "TM1IGNORE_BADTUPLES" function which allows the MDX view to still work after it tries to find your calculated member but can't display it because there isn't a "WITH MEMBER" clause for that view.

    You will be able to force it to work in most cases by copying and pasting the "WITH MEMBER" section from where the calculation was created to the MDX of all other relevant views in your PAW book. But I expect there will be some cases where this could cause errors itself, for example one view that contains the dimension element may be returning a string value and another a numeric value... if you try doing a numeric calculation on the string values it will obviously be an issue.
    I expect that this is part of why there isn't functionality to replicate your calculation across views automatically built in to PAW.

    One benefit of this, is that you can use the same calculated member name to do different things for different views. Particularly useful when you want to have a generic name such as "Subtotal" for different tabs that may be in the same measures dimension etc.

    To summarise, the sync does work between views but you need to add a "WITH MEMBER" section to the MDX of the views that you want it to work for.
    Its worth noting that the view will remember and keep your "WITH MEMBER" section (whether created through the right-click GUI or via writing/pasting in the MDX box) even if you then change the set of elements displayed to not include the calculated member, so that way you can filter away from your calculated member and back to it and it will still remember what calculation should be applied.



    Declan Rodger
    Technical Director
    Spitfire Analytics

  • 3.  RE: Synchronize Calculated item between sheets in PAW

    Posted Sun September 05, 2021 12:54 AM
    Thanks Declan for your quick response.

    I will try to implement your suggestion as this calculated item is for variance analysis of numeric values against different versions.

    Thanks & Regards

    Sumit Suman