Sorting a pivot table with 2 dimensions

[kkkumar82]

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.

[vvira1316]

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

[kkkumar82]

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)))

[sunny_talwar]

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

[kkkumar82]

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

[sunny_talwar]

Check this

Dimension

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

[kkkumar82]

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.

[sunny_talwar]

ok