Parameters in set analysis

[Anonymous]

Hi All,

When trying to use a parameter in the set analysis facing some issues.

below is the variable am creating

set vExpression =

num(Avg({<[KPI] = {$1},Year=,Month=,Day=,[Date],[Date]={">=$(=Num($(=vToDate& 'Start(Max(Date))'))) <=$(=Max(Date))"}>} Actual),$3)

and using =$(vExpression ('KPI1','#.##')). But it's not working. When I placed it in straight table and hovered the expression

>=$(=Num($(=vToDate& 'Start(Max(Date))'))) <=$(=Max(Date)) is being showed as >= <=.

I also tried passing 'Start(Max(Date))' as $3 . But the result is same.

The value of vToDate will be Day/Week/Month

Can someone explain what I am doing wrong.

[Anonymous]

I think we can use something like

set vExpression =

num(Avg({<[KPI] = {$1},Year=,Month=,Day=,[Date],[Date]={">=$(=Num($(='Month'& $2))) <=$(=Max(Date))"}>} Actual),$3)

and used $(vExpression ('KPI1','Start(Max(Date))','#.##')).

[sunny_talwar · Accepted solution]

Check the attached now

[Anonymous]

Can you please explain what was the issue?? Why does giving "$" in the vexpression doesn't work.

[sunny_talwar]

$ sign gets expanded during the reload and causes it to be null..

Avoid Dollar sign expansion when reloading | Qlik Community‌

Stop Dollar Sign Expansion in the script (Escape Character ??? )

[Anonymous]

Sunny, but when i use variable vTodate instead of hardcoded 'Month',the expression doesnt work.

[sunny_talwar]

Can you share the updated sample?

[Anonymous]

Please check

[sunny_talwar]

Sorry, but I am a litte confused... you are comparing Date to text? =Month& something? What is the point?

[Anonymous]

When i use Month&StartDate(Date), i get the month start, similary, Weekstart, Yearstart() will work when i update the value of vTodate to Week/Day/Month/Year. I implemented this to calulate YTD,MTD,WTD values.

[sunny_talwar]

Oh okay... I see what you are doing.....