Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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

1 Reply
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.