1 Reply Latest reply: May 31, 2017 5:56 PM by Ruben Marin RSS

    How to set up modifier in a variable for a set analysis

    Ying Zhou

      Hi All,


      Hope you are doing well!


      I have a very tricky question regarding variables, parameters, modifiers and set analysis.


      1. I have a variable to calculate the sales for different period (YTD, QTD, MTD), the variable vCalc_Sales = sum({<$($1)>}Sales)

      2. When vCalc_Sales is used in the table measures, I am using the formula: $(vCalc_Sales(vSetQTD))

      Basically, vSetQTD is the parameter that will pass in the calculation of vCalc_Sales

      3. To set up the modifier inside the vCalc_Sales, the expression for vSetQTD is as follows:



      %PeriodID is the monthID, and %QuarterID is QuarterID


      But when I write the formula in the table $(vClac_Sales(vSetQTD)), everything returns to 0.


      Can anyone advise where I did wrong or how I should do it?


      Thanks so much for your help!




        • Re: How to set up modifier in a variable for a set analysis
          Ruben Marin

          Hi, I first would try to set this expression in a table without title for the expression, then hovering the mouse over the column title you can check if it's expanded as expected.


          I see a typo, the first max needs a parenthesys:

          vSetQTD='%PeriodID={"<='&Chr(36)&'(=MAX(%PeriodID))"}, %QuarterID={'&CHR(36)&'(=max(%QuarterID))}'

          If still doesn't works I would try to wirte the expected expression in another column, when this expression works you only need to do you expression using variables to return a similar result when it's expanded.