Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
3 Keynotes, 100+ Breakout Sessions, 1 New York Times bestseller, and you. QlikWorld Online, May 10-12: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
baruchjacob
Contributor III
Contributor III

Pivot Table Sorting - Qlik sense

Hi,

I Have ש pivot table with three dimensions and one expression,

the table is fully expanded and the third dimension shows only top 10.

I want that the third dimension will be in descending order by the expression.

any idea.

Thanks,

Jacob

1 Solution

Accepted Solutions
luismadriz
Specialist
Specialist

Ok Jacob,

For the second and third dimension, please replace them with the following respectively:

=AGGR(DUAL(Group,RowNo(TOTAL)),Shop,Group)

=AGGR(DUAL(Item,RowNo(TOTAL)),Shop,Group,Item)


That will make each value unique within each subgroup


I hope this helps,


Please try and let me know,


Cheers,


Luis

View solution in original post

27 Replies
OmarBenSalem
Partner
Partner

Go to the sort tab; go to ur 3rd dimension;

sort it by expression; tap the measure u're using and sort it as descending:

Capture.PNG

baruchjacob
Contributor III
Contributor III
Author

Hi,

Thanks for the advise, unfortunately it isn't working.

luismadriz
Specialist
Specialist

Hi Jacob,

If the values in your dimensions can appear in different groups then the solution that Omar is showing may need some tweaking. Can you share a bit more about it?

Cheers,

Luis

baruchjacob
Contributor III
Contributor III
Author

Yes, The third dimension can appear more than once for example

Shop   Group  Item     Qty

A          I           X          54

                        Y          58    

                        Z          35

B          I           X          85

                        T           42

                         R          23

             II          S          55

                         M         22

                         L          15 



Thanks.

luismadriz
Specialist
Specialist

Ok Jacob,

For the second and third dimension, please replace them with the following respectively:

=AGGR(DUAL(Group,RowNo(TOTAL)),Shop,Group)

=AGGR(DUAL(Item,RowNo(TOTAL)),Shop,Group,Item)


That will make each value unique within each subgroup


I hope this helps,


Please try and let me know,


Cheers,


Luis

View solution in original post

baruchjacob
Contributor III
Contributor III
Author

Thanks, but still not working

you might forgot to mention: sum(qty) any where?

luismadriz
Specialist
Specialist

Hi,

Yeap you're right. I'm assuming you're following Omar's recommendation (Sorting by expression with Sum, etc.)

It's the same that Omar said but the 2nd and 3rd dimensions on your Pivot Table needed to be changed

Please try and let me know

Cheers,

Luis

baruchjacob
Contributor III
Contributor III
Author

Hi,

Tried without a success.

=AGGR(DUAL(Group,RowNo(sum(TOTAL Qty))),Shop,Group)

=AGGR(DUAL(Item,RowNo(sum(TOTAL Qty))),Shop,Group,Item)

luismadriz
Specialist
Specialist

Hehe, not like that,

Is it possible for you to share data?