3 Replies Latest reply: Jan 19, 2018 6:22 PM by Casey Marincin RSS

    how to bucketize field values for a measure

    Casey Marincin

      I am working in Qlik Sense 3.2.  I have the following sample source table:

      T1:

      Load * Inline [

      ID, Days, Price

      1, 2, $5

      2, 11, $10

      3, 10, $2

      4, 30, $30

      5, 32, $100

      6, 17, $25

      7, 25, $32

      8, 3, $37

      9, 46, $25

      10, 48, $17

      ]

      ;

       

      I need to create a dimension called 'DayCategory' that "bucketizes" the Days field by ranges (maps the ranges to single values).  The ranges and output values are:

      0 - 10     a

      11 - 20     b

      21 - 30     c

      31 - 40     d

      41 - 50     e

       

      I will then be using this dimension in a combo chart that has measures on fields ID and Price.

       

      How can I create this new field / dimension in the load script?  I know that the most intuitive answer is to brute-force it with nested if statements.  That won't work for my particular situation, though, because I have far more bucket ranges than what I listed in the dummy data above.  I don't think Pick-Match or ApplyMap will work either because those solutions work for one-to-one value mappings.  I am aware of the Class chart function, but that won't work because I am not measuring on field Days.  So, I'm at a loss.

        • Re: how to bucketize field values for a measure
          Petter Skjolden

          The function Class() should do it for you - like this:

           

          Class(Days,10,'Days',1)

           

          Actually this will have binsize of 10 and your list has the first one with binsize of 11 and the rest 10... so it won't really work exactly as you want. But then again 0 days is not present in your data so why 0-10 ?

            • Re: how to bucketize field values for a measure
              Casey Marincin

              The data I presented is a very contrived simplification of my actual data.  (I am unable to post my actual data for proprietary reasons.)  My actual bin intervals and desired output are:

              0 - 90          <1/4 Year

              91 - 180      1/4 - 1/2 Year

              181 - 270     1/2 - 3/4 Year

              271 - 365     3/4 - 1 Year

              >365             >1 Year

               

              My actual Day values span 0 to 365+.  I presented a simplification as I thought that any solution proposed would be independent of the actual distribution of data and desired dimension values.  So, I don't think Class() will work for me.

            • Re: how to bucketize field values for a measure
              Petter Skjolden

              If you need it exactly like your bucket categories then you could use this expression to create a new field in your load statement in the load script:

               

              If(Days>=0 AND Days<=10,Dual('0-10',0),Dual((Div(Days,10)*10+1)&'-'&(Div(Days,10)+1)*10,Div(Days,10)))