Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Set Analysis - Dynamic field within Set Array

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

Tags (1)
1 Solution

Accepted Solutions
Employee
Employee

Re: Set Analysis - Dynamic field within Set Array

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.

2 Replies
Employee
Employee

Re: Set Analysis - Dynamic field within Set Array

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.

Not applicable

Re: Set Analysis - Dynamic field within Set Array

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!