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

    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

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