topic Long IF-formula in QlikView

Long IF-formula

Fri, 11 Mar 2011 11:28:00 GMT

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

pljsoftware — Fri, 11 Mar 2011 15:35:30 GMT

Hi,

try to pre-calculate yours data.

Long IF-formula

Fri, 11 Mar 2011 16:18:04 GMT

Hi,

Could you please explain how can I do it?

Thx

Long IF-formula

johnw — Sat, 12 Mar 2011 02:00:21 GMT

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)