80-20 Grouping not working in QlikVew version 11

[Hariprasath2394]

Hello All,
I am new to Qlikview and I am working on 80-20(Pareto) Analysis and Quadrant map in QlikView.

I replicated the below link which explains it very well and my 80-20 chart for Customers and Products works fine

https://community.qlik.com/t5/Qlik-Design-Blog/Recipe-for-a-Pareto-Analysis/ba-p/1468497

Now, I am trying to match Customer_Number from Straight Table 1 and Product_Line from Straight Table 2 based on its Class and create four Quadrants that shows number of Customers & products from Class AA, Class AB, Class BA, and Class BB

I followed the below link to do that but unfortunately my Qlikview version does not support the expression.

https://community.qlik.com/t5/Qlik-Design-Blog/Recipe-for-a-Pareto-Analysis-Revisited/ba-p/1473684

The expressions that are not working in my Qlikview version 11 are

Customers:

=Aggr(If(Rangesum(Above(Sum({1} Standard_Profit)/Sum({1} total Standard_Profit),1,RowNo()))<=0.8, 'A','B'),
(Customer_Number,(=Sum({1}Standard_Profit),Desc)))

Products:

=Aggr(If(Rangesum(Above(Sum({1} Standard_Profit)/Sum({1} total Standard_Profit),1,RowNo()))<=0.8, 'A','B'),
(Product_Line,(=Sum({1}Standard_Profit),Desc)))

To Join the classes between Customers and Products:

 =Aggr(NODISTINCT If(Rangesum(Above(Sum({1} Standard_Profit)/Sum({1} total Standard_Profit),1,RowNo()))<=0.8, 'A','B'),
(Customer_Number,(=Sum({1}Standard_Profit),Desc)))

&

=Aggr(NODISTINCT If(Rangesum(Above(Sum({1} Standard_Profit)/Sum({1} total Standard_Profit),1,RowNo()))<=0.8, 'A','B'),
(Product_Line,(=Sum({1}Standard_Profit),Desc)))

Now, I must group the classes AA, AB, BA, BB and create four lists that shows -

Quadrant 1: List of Customer Numbers with class 'A' from Straight Table 1 that has Product Line Class 'A' from straight table 2. Eg., Customer_Numbers in class A that has Product_Line in class A

Quadrant 2: List of Customer Number with class 'A' from Straight Table 1 that has Product Line Class 'B' from straight table 2. Eg., Customer_Numbers in class A that has Product_Line in class B

Quadrant 3: List of Customer Number with class 'B' from Straight Table 1 that has Product Line Class 'A' from straight table 2 Eg., Customer_Numbers in class B that has Product_Line in class A

Quadrant 4: List of Customer Number with class 'B' from Straight Table 1 that has Product Line Class 'B' from straight table 2. Eg., Customer_Numbers in class B that has Product_Line in class B

Can anyone please help me? It is very important for my project and a little urgent one. I would highly appreciate your help. I have attached my file for your reference Thanks a lot, in advance.

[marcus_sommer]

Your second one didn't work because the used aggr() has a sorting-option. This feature was only introduced with QlikView 12. Means you need to relinquish on this one or to upgrade on QV 12.

- Marcus

[Hariprasath2394]

Thanks for your reply Marcus. Unfortunately my company cannot upgrade the version to QV 12. Can you please tell me whether there is any other script that can be written as an alternative or any other option for this in QV 11?

[marcus_sommer]

Not really. If I remember correctly I tried already something like that but didn't used it in the end but I don't remember anymore why. In each case it will become quite complex and will slowdown the performance significantly ... so it may finally work but be too slow for a practically use.

Nevertheless if you want to play a bit with it you may try a similar approach like mine. If I remember correctly I combined within the calculated dimension also a ranking-information (I think it was at the first position to get them in the right order by the automated string-sorting from QlikView) and used then at least one further outer aggr() to check these ranking-values and to pick the wanted ones and to clean the string again ...

- Marcus

[Hariprasath2394]

I am so sorry Marcus, I am not able to understand it clearly. Can you please share me the file that you have tried if you can so that I can understand it better? Thank You.

[marcus_sommer]

Unfortunately I don't have this application anymore. It's a long time ago probably 10 years or more and we are now 4 or 5 business releases further of our environment and we don't keep such old file within the normal backup.

Only if this task is absolutely essential you should go further with it because it will be a quite ugly and frustrating matter. Did you check if it could be transferred within the script? Yes, it won't be dynamically in regard to any selections but maybe it aren't so many respectively too many variants that they couldn't be pre-calculated.

- Marcus

[Hariprasath2394]

Hello Marcus, Yes it is highly essential for me☹️. I have tried in the script too but no luck. It is just messy. Thing is I would be grateful if I could sort down the list based on descending order without writing a separate expression for cumulative sales & its % and sort down in sort option as we cannot mingle two dimensions. I don't know what I am going to do now as I am completely lost😞. Thanks a lot for replying to me.

[marcus_sommer]

Sorry, but I don't know an easier approach.

Most important by developing unknown solutions is to apply a trial & error approach but not a brute force one else a systematically one. This means to use a very reduced dataset maybe just a few fields and a few records to keep it simple and performant and to slice the tasks into smaller parts and to think about your trials (maybe with a whiteboard or a few simple testing's in Excel) before trying/executing them in QlikView. What do you expect should happens and do the results lead nearer to a solution or rather not.

Most often there are solutions or at least workarounds but of course it could mean quite a lot of work ...

- Marcus

[sunny_talwar]

We tried something before qv12 was launched https://community.qlik.com/t5/New-to-QlikView/ABC-Analysis-in-Qlikview/m-p/1202496, but the final solution was using sorting in aggr() function. But see if some of the responses in the thread work for you

Also, make sure to check this

https://community.qlik.com/t5/QlikView-Documents/So-How-Many-Customers-Make-Up-Most-of-Your-Sales/ta...

[Hariprasath2394]

Thanks a lot for your reply @sunny_talwar . Actually I tried various Aggr() in Qlikview 11 but unfortunately, I am not able to sort inside the expression as there is no NUMERIC function in Version 11, and neither the expression below works.

=Aggr(NODISTINCT If(RangeSum(Above(Sum({1} Standard_Profit), 0, RowNo()))/
Sum({1} TOTAL Standard_Profit) <= 0.8, 'A', 'B'), (Product_Line,(=Sum({1} Standard_Profit),Desc)))

So I took it to QlikView 12 to check the same. Fortunately, the expression works even if it errors for ",Desc". 

But I have a new problem here.

My requirement is to create Quadrants after grouping 80-20.

Quadrant 1 - Customer A and Product A,

Quadrant 2 - Customer A and Product B,

Quadrant 3 - Customer B and Product A,

Quadrant 4 - Customer B and Product B.

I have grouped the 80-20 (Class A & Class B) for Customers and Products separately as you can see below.

  

Next, I wanted to combine both Customer and Product to show as 'AA' or 'BB' or 'AB' or "BA'.  I used & in the third expression and even if everything shows as error after ,Desc the expression works fine as you can see below.

Now, I have two issues for which I hope that I could use your help.

1. I want to create 4 straight Tables which show Quadrant 1, Quadrant 2, Quadrant 3, and Quadrant 4.

For Quadrant 1, I gave the Dimension - Customer Number and Product Line. In expression, I gave Aggr() for Customer as expression 1 and Aggr() for product as expression 2  and If(Column(1) & Column(2) = 'AA', Sum(Standard_Profit)) as expression 3. Then I sorted down based on Column numeric value Descending. 

The problem here is I want the data to be shown only for 'AA' and not for any other combination.

What is the else condition in If() to eliminate the other combinations('AB', 'BA', 'BB')? 

Like "If(Column(1) & Column(2) = 'AA', Sum(Standard_Profit), hide)"

The same is for other Quadrants. I wanted to see only 'AA' in Quad 1, 'AB' in Quad 2, 'BA' in Quad 3 and 'BB' in Quad 4.

2. I want to show these values in Text Object too. I mean the total value. Below is an example of how it should look like. 

Thanks to your solution in https://community.qlik.com/t5/New-to-QlikView/ABC-Analysis-in-Qlikview/m-p/1202496. I have done the same and created a straight table to combine the group and show the total values.

But as per my requirement, I need to show this in text object like the example I showed you above. I have tried the below expression but it is not working. 

=If(Aggr(NODISTINCT If(RangeSum(Above(Sum({1} Standard_Profit), 0, RowNo()))/
Sum({1} TOTAL Standard_Profit) <= 0.8, 'A', 'B'), (Customer_Number,(=Sum({1} Standard_Profit),Desc)))
&
Aggr(NODISTINCT If(RangeSum(Above(Sum({1} Standard_Profit), 0, RowNo()))/
Sum({1} TOTAL Standard_Profit) <= 0.8, 'A', 'B'), (Product_Line,(=Sum({1} Standard_Profit),Desc))) = 'AA', Sum(TOTAL Standard_Profit))

I am confused and trying to find a way how to show the total values in Text Object. Os is there any other way to replicate the value from straight table into text object?

I really hope experts like you and @marcus_sommer  can help me with this. I have attached my file here for your reference. Thanks a lot in advance for your help.