3 Replies Latest reply: Mar 11, 2011 9:00 PM by John Witherspoon RSS

    Long IF-formula

      Hi,

      I need to count "cumulated contribution" - qty of RMA_NO in Total Qty per TAT Group.

       

      When I use formula below on big amount of data it makes it really slow. Could somebody help me with other solution?

      if([TAT Group]='00 ≤ TAT ≤ 01',



      Count (RMA_NO)/Count (total <Week,COUNTRY, RMA_CENTER> RMA_NO),

      (if([TAT Group]='01 ≤ TAT ≤ 05',

      (Count (RMA_NO)/Count (total <Week,COUNTRY, RMA_CENTER>RMA_NO))

      + above(Count (RMA_NO)/Count (total <Week,COUNTRY, RMA_CENTER>RMA_NO)),

      (if([TAT Group]='05 < TAT ≤ 07',

      (Count (RMA_NO)/Count (total <Week,COUNTRY, RMA_CENTER>RMA_NO))

      + above(Count (RMA_NO)/Count (total <Week,COUNTRY, RMA_CENTER>RMA_NO))

      + above(Count (RMA_NO)/Count (total <Week,COUNTRY, RMA_CENTER>RMA_NO),2),

      if([TAT Group]='07 < TAT ≤ 10',

      (Count (RMA_NO)/Count (total <Week,COUNTRY, RMA_CENTER>RMA_NO))

      + above(Count (RMA_NO)/Count (total <Week,COUNTRY, RMA_CENTER>RMA_NO))

      + above(Count (RMA_NO)/Count (total <Week,COUNTRY, RMA_CENTER>RMA_NO),2)

      + above(Count (RMA_NO)/Count (total <Week,COUNTRY, RMA_CENTER>RMA_NO),3),

      if([TAT Group]='10 < TAT ≤ 15',

      (Count (RMA_NO)/Count (total <Week,COUNTRY, RMA_CENTER>RMA_NO))

      + above(Count (RMA_NO)/Count (total <Week,COUNTRY, RMA_CENTER>RMA_NO))

      + above(Count (RMA_NO)/Count (total <Week,COUNTRY, RMA_CENTER>RMA_NO),2)

      + above(Count (RMA_NO)/Count (total <Week,COUNTRY, RMA_CENTER>RMA_NO),3)

      + above(Count (RMA_NO)/Count (total <Week,COUNTRY, RMA_CENTER>RMA_NO),4),

      if([TAT Group]='15 < TAT ≤ 20',

      (Count (RMA_NO)/Count (total <Week,COUNTRY, RMA_CENTER>RMA_NO))

      + above(Count (RMA_NO)/Count (total <Week,COUNTRY, RMA_CENTER>RMA_NO))

      + above(Count (RMA_NO)/Count (total <Week,COUNTRY, RMA_CENTER>RMA_NO),2)

      + above(Count (RMA_NO)/Count (total <Week,COUNTRY, RMA_CENTER>RMA_NO),3)

      + above(Count (RMA_NO)/Count (total <Week,COUNTRY, RMA_CENTER>RMA_NO),4)

      + above(Count (RMA_NO)/Count (total <Week,COUNTRY, RMA_CENTER>RMA_NO),5),

      if([TAT Group]='Above 20',

      (Count (RMA_NO)/Count (total <Week,COUNTRY, RMA_CENTER>RMA_NO))

      + above(Count (RMA_NO)/Count (total <Week,COUNTRY, RMA_CENTER>RMA_NO))

      + above(Count (RMA_NO)/Count (total <Week,COUNTRY, RMA_CENTER>RMA_NO),2)

      + above(Count (RMA_NO)/Count (total <Week,COUNTRY, RMA_CENTER>RMA_NO),3)

      + above(Count (RMA_NO)/Count (total <Week,COUNTRY, RMA_CENTER>RMA_NO),4)

      + above(Count (RMA_NO)/Count (total <Week,COUNTRY, RMA_CENTER>RMA_NO),5)

      + above(Count (RMA_NO)/Count (total <Week,COUNTRY, RMA_CENTER>RMA_NO),6)

      )))))))))

       



        • Long IF-formula
          Luca Jonathan Panetta

          Hi,

          try to pre-calculate yours data.

            • Long IF-formula

              Hi,

              Could you please explain how can I do it?

              Thx

                • Long IF-formula
                  John Witherspoon

                  This produces the same result, and might be fast enough:

                  rangesum(above(count(RMA_NO),0,rowno()))/count(total <Week,COUNTRY, RMA_CENTER>RMA_NO)

                  If it isn't fast enough, I still wouldn't do the accumulation itself in the script. Always (if possible) keep your data at the lowest level of detail and do no script accumulations.

                  But you could create a data structure in the script to allow QlikView to do this more efficiently (and not require you to have every row in the table and in the right sequence, which is what above() requires).

                  [TAT Group Accumulation]:
                  TAT Group Accum, Accum Type, TAT Group
                  00 <= TAT <= 01, None, 00 <= TAT <= 01
                  00 <= TAT <= 01, Full, 00 <= TAT <= 01
                  01 <= TAT <= 05, None, 01 <= TAT <= 05
                  01 <= TAT <= 05, Full, 01 <= TAT <= 05
                  01 <= TAT <= 05, Full, 00 <= TAT <= 01
                  05 < TAT <= 07, None, 05 < TAT <= 07
                  05 < TAT <= 07, Full, 05 < TAT <= 07
                  05 < TAT <= 07, Full, 01 <= TAT <= 05
                  05 < TAT <= 07, Full, 00 <= TAT <= 01
                  etc.

                  Then you use TAT Group Accum instead of TAT Group in the chart.

                  Qty = count({<"Accum Type"={'None'}>} RMA_NO)
                  Contribution 3 = count({<"Accum Type"={'Full'}>} RMA_NO)
                  / count(total <Week,COUNTRY,RMA_CENTER> RMA_NO)

                  For that matter, you could have an Accum Type of 'Total' that did the same as the count(total...), allowing you to not have to specify the dimensions of your chart and to keep a consistent format in the expressions. Not sure it's worth it, but:

                  [TAT Group Accumulation]:
                  TAT Group Accum, Accum Type, TAT Group
                  00 <= TAT <= 01, None, 00 <= TAT <= 01
                  00 <= TAT <= 01, Full, 00 <= TAT <= 01
                  00 <= TAT <= 01, Total, 00 <= TAT <= 01
                  00 <= TAT <= 01, Total, 01 <= TAT <= 05
                  00 <= TAT <= 01, Total, 05 < TAT <= 07
                  00 <= TAT <= 01, Total, 07 < TAT <= 10
                  00 <= TAT <= 01, Total, 10 < TAT <= 15
                  00 <= TAT <= 01, Total, 15 < TAT <= 20
                  00 <= TAT <= 01, Total, Above 20
                  01 <= TAT <= 05, None, 01 <= TAT <= 05
                  01 <= TAT <= 05, Full, 01 <= TAT <= 05
                  01 <= TAT <= 05, Full, 00 <= TAT <= 01
                  01 <= TAT <= 05, Total, 00 <= TAT <= 01
                  01 <= TAT <= 05, Total, 01 <= TAT <= 05
                  01 <= TAT <= 05, Total, 05 < TAT <= 07
                  01 <= TAT <= 05, Total, 07 < TAT <= 10
                  01 <= TAT <= 05, Total, 10 < TAT <= 15
                  01 <= TAT <= 05, Total, 15 < TAT <= 20
                  01 <= TAT <= 05, Total, Above 20
                  etc.

                  Qty = count({<"Accum Type"={'None'}>} RMA_NO)
                  Contribution 3 = count({<"Accum Type"={'Full'}>} RMA_NO)
                  / count({<"Accum Type"={'Total'}>} RMA_NO)