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:

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

       

      %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!

       

      Thanks,

      Gloria

        • 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.