Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Optimisation with RangeSum / Aggr for double pareto ranking ?

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))
)

Labels (3)
1 Reply
Highlighted

Have you considered using AsOfTable instead of doing RangeSum(Above()) in the script? With AsOfTable you will still get the flexibility of changing values based on selections while getting better performance on the front end