Set Analysis - Dynamic field within Set Array

[#1]

Hi,

I am evaluating for the lowest sales figure for the past x number of days.

Using set analysis, I am trying to get my maximum date to be subtracted by a number from a field. For example if I need the maximum date minus 7 days then this expression works:

Min( {$< Calender__DateSerial = {"> $(=Max(Calender__DateSerial) - 7 )"} >}  Sales)

However, I would like the number 7 to be replaced by a number from a given field:

Min( {$< Calender__DateSerial = {"> $(=Max(Calender__DateSerial) - [FieldWithNumber] )"} >}  Sales)

How can I get Qlik Sense to evaluate the number form the field? Note, I would be selecting the row that provides the number for the field in another place.

Thanks,

Mike

[JonnyPoole · Accepted solution]

I think you need to use an aggregation function around [FieldWithNumber] .and i would suggest using a variable as well

Variable                         Formula

vNumberSelection          =only([FieldWithNumber])

The only() function returns Null() if there is more than one possibility for FieldWithNumber. But if there is only 1 selection, it aggregates to return that only value. Sounds like it might work for you.

Then reference the variable in the Set Statement:

Min( {$< Calender__DateSerial = {"> $(=Max(Calender__DateSerial) - VNumberSelection) "} >}  Sales)

If you are using 2.1 you can define variables direct from the UI (there is a little button at the bottom in Edit mode.

[#3]

Thanks Jon,

The new Variable input in the UI had escaped my attention! I suddenly found a stack of old variables hanging around in my app....

You didnt so much answer my question as point me to what my question should be, and therefore the answer. Much appreciated!