Dear all expert,
I have some performance problem I can't manage to solve
I have the requirement to qualify a list of product based on two rank:
- "quantity sold" with rank A/B/C (less 80% of all quantity sold = C, from 80 to 95% = B, A for the rest)
- "number of sales order" with rank F/M/R with the same rules (F < 80 < M < 95 < R)
these two classification needs to be applied dynamically depending on filter applied (so I can't precalculate)
We are on QlikView 12.1 SR8
Test have been done on production server with 16 virtual processor x 128 Go Ram
Displaying the graph without filter takes around 1 to 3 minutes which is not acceptable
I removed all uneeded fields as well as most of the set analysis, tried to replace char by numeric. I kept the minimum amount of lines by doing a group by during loading (it remains around 400K lines per table)
Do you have any idea to lower calculation time ? See calculation below and attached sample qvw
Thanks in advance
Nicolas
ABC Classification
=Aggr(
If(Rangesum(Above(Sum( {$<Product_Code=>} _Sales_Order_Quantity)
/Sum( {$<Product_Code=>} total _Sales_Order_Quantity),
0,RowNo()))<0.8, 'A',
If(Rangesum(Above(Sum( {$<Product_Code=>} _Sales_Order_Quantity)
/Sum( {$<Product_Code=>} total _Sales_Order_Quantity),
0,RowNo()))<0.95, 'B',
'C')),
(Product_Code,(=Sum( {$<Product_Code=>} _Sales_Order_Quantity),Desc))
)
FMR Classification (quite the same code)
=Aggr(
If(Rangesum(Above(count( {$<Product_Code=>} TEST_SO)
/count( {$<Product_Code=>} total TEST_SO),
0,RowNo()))<0.8, 'F',
If(Rangesum(Above(count( {$<Product_Code=>} TEST_SO)
/count( {$<Product_Code=>} total TEST_SO),
0,RowNo()))<0.95, 'M',
'R')),
(Product_Code,(=count( {$<Product_Code=>} TEST_SO),Desc))
)
