Sorting within pivot tables

[#1]

I have created a pivot table with the dimensions payer, product and month, where months are in columns. The expression is the total sales column. I want the payers to be sorted based on latest month's highest sales volume and within payers the products should be sorted based on the descending order of the sales for each product in latest month. I have used expression to sort the data  sum (if(monthdate=max(monthdate), sales). Could you please advise on how I should set up the sorting?

[ramoncova06]

under the sort section your first field has to be the month, then payer and then product

you can change your expression to the following sum({< monthdate = {"$(=(max(monthdate))"}>}sales) (this is also faster than using if's)

[#3]

Thank you Ramon!

I used this expression and it is sorting the first dimension i.e. payer but is it not sorting products within each payer. I also want the products within each payer sorted in descending order.

[ramoncova06]

did you add the expression to the products ?

[#5]

Yes, I tried that. I have also tried aggr(sum({< monthdate = {"$(=(max(monthdate))"}>}sales),payer). It is also not working.

[ramoncova06]

Not in front of a computer right not but try by using a rank(expression)

[Anonymous]

Not possible.  You cannot have 2nd (3rd, etc.) dimension to be sorted differently in each group.

[vikasmahajan]

You can create Straight table instead of pivot , This are faster in performance also.

Vikas

[sunilkumarqv]

Hi swatsaraora,

Take your measure expression and then put that expression in sort it in descending order

and exclude  other options

example

sum({<Year={"$(=Max(Year))"},Month=,Qtr=,Date=,Products=>}Amount )