topic Re: Pareto - accumulated sum - sorting issue in QlikView

Pareto - accumulated sum - sorting issue

MS_UK — Thu, 03 Oct 2019 20:31:02 GMT

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

Re: Pareto - accumulated sum - sorting issue

chrismarlow — Mon, 07 Oct 2019 19:49:43 GMT

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.

Re: Pareto - accumulated sum - sorting issue

qliksus — Tue, 08 Oct 2019 05:08:39 GMT

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

Re: Pareto - accumulated sum - sorting issue

jaibau1993 — Tue, 08 Oct 2019 06:29:25 GMT

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.