topic Parameters in set analysis in QlikView

Parameters in set analysis

Anonymous — Mon, 20 Mar 2017 14:14:21 GMT

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.

Re: Parameters in set analysis

sunny_talwar — Mon, 20 Mar 2017 14:17:37 GMT

Where is $2? You have used $1 and $3?

Re: Parameters in set analysis

Anonymous — Mon, 20 Mar 2017 14:24:08 GMT

Sorry Sunny,

At first i tried,

set vExpression =

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

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

Later I tried ,

set vExpression =

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

and used $(vExpression ('KPI1','#.##')).

Neither of them worked.

Re: Parameters in set analysis

sunny_talwar — Mon, 20 Mar 2017 14:27:15 GMT

Would you be able to share a sample?

Re: Parameters in set analysis

Anonymous — Mon, 20 Mar 2017 14:51:27 GMT

Please find the attachment.

When hovered on the straight table, the value is incorrect.

Re: Parameters in set analysis

sunny_talwar — Mon, 20 Mar 2017 14:59:34 GMT

But where is $2? I am not sure what the idea is here? Where does Start(Max(Date)) this go?

Re: Parameters in set analysis

Anonymous — Mon, 20 Mar 2017 15:06:26 GMT

In the variable vexpression code, you can find $1,$2,$3. Am not sure if i missed anything.

Re: Parameters in set analysis

sunny_talwar — Mon, 20 Mar 2017 15:10:19 GMT

Got it... and what is vToDate here?

Re: Parameters in set analysis

Anonymous — Mon, 20 Mar 2017 15:12:55 GMT

Its value will be Day/Month/Year based on button clicks which i didnt implement in the sample qvw.

Re: Parameters in set analysis

sunny_talwar — Mon, 20 Mar 2017 15:13:54 GMT

Can you give me a static value to test this out?

Re: Parameters in set analysis

Anonymous — Mon, 20 Mar 2017 15:16:36 GMT

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))','#.##')).

Re: Parameters in set analysis

sunny_talwar — Mon, 20 Mar 2017 15:19:47 GMT

Check the attached now

Re: Parameters in set analysis

Anonymous — Mon, 20 Mar 2017 15:22:50 GMT

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

Re: Parameters in set analysis

sunny_talwar — Mon, 20 Mar 2017 15:25:20 GMT

$ 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 ??? )

Re: Parameters in set analysis

Anonymous — Mon, 20 Mar 2017 15:45:13 GMT

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

Re: Parameters in set analysis

sunny_talwar — Mon, 20 Mar 2017 15:47:12 GMT

Can you share the updated sample?

Re: Parameters in set analysis

Anonymous — Mon, 20 Mar 2017 15:50:17 GMT

Please check

Re: Parameters in set analysis

sunny_talwar — Mon, 20 Mar 2017 16:01:17 GMT

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

Re: Parameters in set analysis

Anonymous — Mon, 20 Mar 2017 16:19:54 GMT

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.

Re: Parameters in set analysis

sunny_talwar — Mon, 20 Mar 2017 16:26:08 GMT

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