topic Re: Sorting in a pivot table in QlikView

Sorting in a pivot table

paolojolly — Wed, 25 Nov 2020 16:16:04 GMT

Hi, I need help in qlikview document to sort data in a PivotTable
I have pivot table with Item as dimension and two expression: first is the sum of value for year 2017 and second is sum of value for year 2016

ITEM	VALUEYEAR2017	VALUEYEAR2016
Q6	0	0,266558808
Q5	0	0,30819209
Q4	0	0,339399076
Q1	0	0,46405547
QQQ	0	0,508628659
VVV	0	0,640647149
GGG	1,055279918	1,129666153
FFF	1,289558295	1,212542373
PPPP	0	1,316492039
EEE	2,034564879	2,793871192
CCC	3,092783303	2,967093559
DDD	2,266337956	3,058289676
HHH	0	3,189853571
BBB	9,772478033	9,902400863
AAA	37,33924193	50,40652062

I need to sort data first for the sum of value for year 2017 in descending order and then, when values of this expression is <= to zero, to sorting in descending order for the second expression value(VALUEYEAR2016)

I need to get to the following result

ITEM	VALUEYEAR2017	VALUEYEAR2016
AAA	37,33924193	50,40652062
BBB	9,772478033	9,902400863
CCC	3,092783303	2,967093559
DDD	2,266337956	3,058289676
EEE	2,034564879	2,793871192
FFF	1,289558295	1,212542373
GGG	1,055279918	1,129666153
HHH	0	3,189853571
PPPP	0	1,316492039
VVV	0	0,640647149
QQQ	0	0,508628659
Q1	0	0,46405547
Q4	0	0,339399076
Q5	0	0,30819209
Q6	0	0,266558808

Can someone help me?

thanks

Re: Sorting in a pivot table

Anonymous — Wed, 08 Mar 2017 13:46:47 GMT

Hi,

In the Sort tab, promote the Sales 2017 first, then Sales 2016 second.

For both, sort them Numeric Value Descending.

I have an app to show you.

Best regards,

Cosmina

Re: Sorting in a pivot table

adamdavi3s — Wed, 08 Mar 2017 14:44:42 GMT

Sorting in pivots is a pain, Cosmina is right in that you probably want to use a straight table for this!

Re: Sorting in a pivot table

adamdavi3s — Wed, 08 Mar 2017 14:51:56 GMT

Ok a compound formula will do it.

Sort your dimension by expression descending and use this:

=sum(VALUEYEAR2017)*(max({1}VALUEYEAR2017)*1000) + sum(VALUEYEAR2016)

Re: Sorting in a pivot table

paolojolly — Thu, 09 Mar 2017 06:31:59 GMT

Thank you all for the help

Re: Sorting in a pivot table

adamdavi3s — Thu, 09 Mar 2017 07:28:16 GMT

Did this work out for you?

If so please remember to mark this as helpful or the correct answer if I have helped you or answered your question.

This will help users identify the answers should they come across this thread in the future.