8 Replies Latest reply: Nov 30, 2015 6:03 PM by Michael Cox RSS

    Set analysis: exclude all except 1 dimension + set 1 dimension

    Michael Cox

      After scouring the community, I still can't get this to work - any and all ideas would be appreciated!

       

      Requirement:  always display a specific KPI (Alias=CTR130) for a specific year (set in a variable called vCY). The result should NOT change when selections are made, except where the user makes a selection in one dimension (Entity Name).

       

      I can sucessfully get the following script to work:

       

      sum({1<[Entity Name]=p([Entity Name]), Year={'2015'}, Alias={'CTR120'}>} Value)

       

      However ... when I try to replace the year 2015 with my variable, the result (incorrectly) changes when I select different years. It presumably has to do with the $.

       

      sum({1<[Entity Name]=p([Entity Name]), Year={$(vCY)}, Alias={'CTR120'}>} Value)

       

      Can anyone help to show me how to use a variable inside this set analysis?

       

      PS I was hoping to do this without having to list all of the dimensions that must be excluded.

       

      Thank you!