Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

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

Anonymous
Not applicable
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

Anonymous
Not applicable
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

Anonymous
Not applicable
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

Anonymous
Not applicable
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?