Skip to main content
The way to achieve your own success is the willingness to help somebody else. Go for it!
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Variable in Set Analysis


i use variable for special week number in Set Analysis, but is not functioning as intended. Variable calculate week number as follows:

next week => -1

actual week => 0

previous week => 1

and so on...

Result is integer number.

When i use this variable in table as expression, it works fine. (column Week)

When i use it in Set Analysis result is null. (Total Q Dyn)

When i use formula with set analysis with fixed number instead of variable, it works fine. (Total Q Fixed)

Here is my whole calculation:

1. LET vToday = Date(Today(),'YYYYMMDD');

2. vCurrentWeek = Only( {1<[DateKey]={'$(vToday)'}>} QWNum) - take current week from Calendar

3. vWeekShift = (QWNum - $(=$(vCurrentWeek)))*(-1) - calculate week number in table


and set analysis:

4. SUM({$<[~SName] = {'EBR'}, QWNum =, RepYQW =, [~SnapNum] = {$(vWeekShift)}>} TOTAL (Quantity))

Snapshot Dimension:


I don not know why, that specific variable is not usable in set analysis.

Result should be: for actual week it should count from snapshot with [~SnapNum] = 0, for previous week (1) should count from snapshot with [~SnapNum] = 1 and so on. No calendar filter should apply on this calculation.

Can you help me?


8 Replies

You forget the ' symbol

try this: '$(vWeekShift)'

Not applicable

I already try that, no change.... And it is a number so a don not have to use ' symbol, i supposed.

Not applicable

Hello Jozef,

Try this in set analysis:

[~SnapNum] = {'=$(vWeekShift)'}



Partner - Champion III
Partner - Champion III

vWeekShift is not defined with an aggregation function (like Max()/Min()...), so it will have no unique value in the set expression. Also the set expression and variable expansion happen before the chart is evaluated, so you cannot execute these on a row by row basis as I think you require.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

I try some combinations, how to put that variable into set analysis, but with no success. I think there is something with variable interpretation in set analysis. 😕 I try your idea, but the same result.

Not applicable

uff... ok this is a problem , so i have to find out, how to change the variable...

Partner - Champion III
Partner - Champion III

You know that the data set specified by your set analysis expression is calculated before the table object is calculated? And based on all data that is currently active (including selections and so on) or on the total data set if you used {1} inside your set analysis expression?

If you take that into account, you should be able to copy your variable $(vWeekShift) equally well outside of every other object, like in a text box. Does it return a value then? Knowing that to calculate vWeekShift, you use field QWNum as a first operand and without aggregation function on your entire data set, I wouldn't think so.

BTW you can easily find the troublesome part in a complex variable-based expressions by pulling it apart, putting the pieces in text boxes and observing what happens. In your case, the calculation of vCurrentWeek works like a charm in a text box, but vWeekShift doesn't...


Correct answer by

Jacek Wałaszek