topic Re: Set Analysis with variables in QlikView

Set Analysis with variables

Wed, 03 Sep 2014 16:13:14 GMT

Hi all,

I am trying to change a set analysis to read from a variable instead of a static value. The issue is the Dashboard contains only formulas related to dates. When the data source fails to update thus the data is from more than 1 week back, my Dashboard becomes blank.

FROM:

set EngCurrentWeek = Sum({<[# of Weeks from Current Week] ={'-1'}>}[EngagementsTW#])

SET EngPreviousWeek = Sum({<[# of Weeks from Current Week] ={'-2'}>}[EngagementsTW#])

TO:

//Find Max Date on QVD

Set vMaxWeek = week($(vMaxDate));

Set vCurrentWeek = Week(Today());

Set i = $(vCurrentWeek) - $(vMaxWeek);

set EngCurrentWeek = Sum({<[# of Weeks from Current Week] ={'-($(i))'}>}[EngagementsTW#])

SET EngPreviousWeek = Sum({<[# of Weeks from Current Week] ={'-($(i)-1)'}>}[EngagementsTW#])

The new formulas with the variables are not working. How can the set analysis use the variables? Is this a formatting issue?

Re: Set Analysis with variables

gmoraleswit — Wed, 03 Sep 2014 17:37:14 GMT

Try using 'let' instead of 'set' to define your variables:

Let vMaxWeek = week($(vMaxDate));

Let vCurrentWeek = Week(Today());

Let i = $(vCurrentWeek) - $(vMaxWeek);

Re: Set Analysis with variables

Anonymous — Wed, 03 Sep 2014 17:53:56 GMT

Also try using variable in the set analysis like this:

Sum({<[# of Weeks from Current Week] ={'-($(=$(i)))'}>}[EngagementsTW#])

Re: Set Analysis with variables

Wed, 03 Sep 2014 18:06:51 GMT

Hi,

"SET" works when we need constant values (we can say hard code) and "LET" is used to define variable values from a formula, LET can use funtions like today(), week(), Peek() ... just change SET by LET (as Gabriela said) and you can see funtions week() turns blue and variables $() turns gray.

Best regards.