Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Ok a compound formula will do it.
Sort your dimension by expression descending and use this:
=sum(VALUEYEAR2017)*(max({1}VALUEYEAR2017)*1000) + sum(VALUEYEAR2016)
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
Sorting in pivots is a pain, Cosmina is right in that you probably want to use a straight table for this!
Ok a compound formula will do it.
Sort your dimension by expression descending and use this:
=sum(VALUEYEAR2017)*(max({1}VALUEYEAR2017)*1000) + sum(VALUEYEAR2016)
Thank you all for the help
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.