topic Re: Sorting a pivot table with 2 dimensions in App Development

Sorting a pivot table with 2 dimensions

kkkumar82 — Thu, 17 Jan 2019 14:33:54 GMT

Dear all,

I have created a pivot table with two dimensions ProductID, Salesman and a measure Sum(Sales)

now the issue I am seeing in Qlik Sense pivot table is the values are not sorted correctly, I was trying the sortable aggr but

couldn't get to the point, can any one give the correct syntax where the Sum(Sales) can also be sorted in asc/desc when

the table in full expanded view. see below the issue in red also attaching a qvf file.

Re: Sorting a pivot table with 2 dimensions

vvira1316 — Thu, 17 Jan 2019 14:08:32 GMT

I'm not sure if you will be able to sort it based on expression. Usually you can only sort by dimensions

Re: Sorting a pivot table with 2 dimensions

kkkumar82 — Thu, 17 Jan 2019 14:15:34 GMT

Hi,
In QlikView we used to do with Y value option but in Qlik Sense by expression is the only option I guess but I am not even trying those options I am trying to do in the measure itself by writing an aggr with sortable things some like below...

aggr(sum(Sales), ([Product ID],(Salesman),(NUMERIC, DESC)))

Re: Sorting a pivot table with 2 dimensions

sunny_talwar — Thu, 17 Jan 2019 14:37:22 GMT

Since the same salesman are in different Product ID, how would you want to sort them? Based on there value within a Product ID?

Re: Sorting a pivot table with 2 dimensions

kkkumar82 — Thu, 17 Jan 2019 14:40:50 GMT

Yes Sunny Salesman with in each Product ID based on their sales values

Re: Sorting a pivot table with 2 dimensions

sunny_talwar — Thu, 17 Jan 2019 15:18:58 GMT

Check this

Dimension

=Dual(Salesman&Aggr(NODISTINCT Repeat(' ', Rank([Product ID])), [Product ID]), -Aggr(Sum(Sales), [Product ID], Salesman))

Re: Sorting a pivot table with 2 dimensions

kkkumar82 — Thu, 17 Jan 2019 15:41:05 GMT

Thanks Sunny for the solution

I think I can't go with this as my chart contains 6 dimensions, some of them are Rows and some of them are columns

Aggr might screw up performance.

Re: Sorting a pivot table with 2 dimensions

sunny_talwar — Thu, 17 Jan 2019 15:45:20 GMT