Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.