Pareto - accumulated sum - sorting issue

[MS_UK]

Hi All,

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

I am working on Complaints analysis.  For some of the reasons it doesn't work for me. I have an issue on the very beginning with sorting order. 

When I follow instruction and create Pivot Table, setup sorting order as below

My result you can see in table below.

Second column expression =Sum(Complaint_Value)

Third Column expression =RangeSum(Above(SUM(Complaint_Value), 0, RowNo()))

Complaints Type	=Sum(Complaint_Value)	Accumulated Complaints
type 1	563	601
type 2	42	643
type 3	38	38
type 4	33	703
type 5	27	670
type 6	11	724
type 7	10	713

When I change sorting order as below:

Results are different but still incorrect:

Complaints Type	=Sum(Complaint_Value)	Accumulated Complaints
Type 1	38	38
Type 2	563	601
Type 3	42	643
Type 4	27	670
Type 5	33	703
Type 6	10	713
Type 7	11	724

Could you please advise? I have no more ideas how to solve this issue

Thank you

Best Regards

Michael 

[chrismarlow]

Hi,
I think you need to check 'Expression' as your sort option (3rd down, 2 above Numeric Value from your last screen shot) and enter the expression - Sum(Complaint_Value) - and check Descending.
Cheers,
Chris.

[qliksus]

In the sorting tab under the expression , use  RangeSum(Above(SUM(Complaint_Value), 0, RowNo())) for your sort  if you are looking to sort based on the accumulation . when you select sort based on Y-Value it takes the first expression for the sort which is why you got the sorting of the first expression correctly  

[jaibau1993]

Hi!

When you sort by "Y-Value" in a pivot table, it is sorted following the first expression. Said so, you may switch to straight table so you can sort the chart by any of the columns (I don't see the need of a pivot table in your example so, if you can use a straight table I would do it).

If you need to keep using a pivot, you can create a first column to sort the table (with "RangeSum(Above(SUM(Complaint_Value), 0, RowNo()))" in your case) and then completely hide the column (you can achieve this with a macro).

Let me know if this helps!

Jaime.