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

[#1]

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

[rubenmarin]

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.