Set Analysis with variables

[#1]

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?

[gmoraleswit · Accepted solution]

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

Let vMaxWeek = week($(vMaxDate));

Let vCurrentWeek = Week(Today());

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

[Anonymous]

Also try using variable in the set analysis like this:

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

[#4]

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.