2 Replies Latest reply: Sep 29, 2015 8:28 PM by Mike Fitzgerald RSS

    Set Analysis - Dynamic field within Set Array



      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.





        • Re: Set Analysis - Dynamic field within Set Array
          Jonathan Poole

          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.