topic Re: Variable in Set Analysis in QlikView

Variable in Set Analysis

Mon, 29 May 2017 12:06:51 GMT

Hi,

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?

Thanks.

Re: Variable in Set Analysis

jmmayoral3 — Mon, 29 May 2017 12:15:12 GMT

You forget the ' symbol

try this: '$(vWeekShift)'

Re: Variable in Set Analysis

Mon, 29 May 2017 12:21:01 GMT

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

Re: Variable in Set Analysis

Anonymous — Mon, 29 May 2017 12:36:50 GMT

Hello Jozef,

Try this in set analysis:

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

Regards

Jacek.

Re: Variable in Set Analysis

jonathandienst — Mon, 29 May 2017 12:40:31 GMT

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.

Re: Variable in Set Analysis

Mon, 29 May 2017 12:41:23 GMT

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.

Re: Variable in Set Analysis

Mon, 29 May 2017 12:43:25 GMT

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

Re: Variable in Set Analysis

Peter_Cammaert — Mon, 29 May 2017 12:50:43 GMT

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...

Re: Variable in Set Analysis

dirk1955 — Wed, 16 May 2018 09:36:56 GMT

Correct answer by

topic Re: Variable in Set Analysis in QlikView

Variable in Set Analysis

Re: Variable in Set Analysis

Re: Variable in Set Analysis

Re: Variable in Set Analysis

Re: Variable in Set Analysis

Re: Variable in Set Analysis

Re: Variable in Set Analysis

Re: Variable in Set Analysis

Re: Variable in Set Analysis

Jacek WaÅ‚aszek