Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
For issue 1, I think you are only concentrating on the 3rd expression, but you still have expression 1 and 2 showing values for each row. Straight table will continue to show the rows as long as even a single expression returns a non-null value. To fix this, all you need to do is to use the same if statement which you have for expression 3, in expressions 1 and 2. For example Quadrant 1's 1st expression should now look like
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',
Aggr(NODISTINCT If(RangeSum(Above(Sum({1} Standard_Profit), 1, RowNo()))/
Sum({1} TOTAL Standard_Profit) <= 0.8, 'A','B'), (Customer_Number,(=Sum({1} Standard_Profit),Desc))))And Quadrant 1's 2nd expression will be
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',
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))))Notice how we are all the way at the end and no nulls
Will look at the second issue now and respond in another reply
You can use this to get the totals
='Quadrant 1: ' & Num(Sum(Aggr(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(Standard_Profit)), Customer_Number, Product_Line)), '#,##0.00')
Hi @sunny_talwar . I have one more small question. What is the expression should I use in text object to count the number of DISTINCT Customer and DISTINCT Product for a Quadrant?
For example, I want to show no.of.Customers (96) & no.of.Products (15) as shown below in Quadrant 2 into a text object.
But I am getting the total row value of Customers(224) and Products(224) in text object.
The expression that I used in the text object is:
'Total Customers:' & ' ' &
Num(Sum(Aggr(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))) = 'AB', Count(DISTINCT Customer_Number)), Customer_Number, Product_Line)), '#,##0')
This will calculate the Sum value of Customers & Products but even if I write Count instead of Sum in the beginning and remove the Product Line at last, it is not working.
Can you please help me? Thank you in advance.
