topic Re: Sorting within pivot tables in QlikView

Sorting within pivot tables

Thu, 07 May 2015 22:35:29 GMT

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?

Re: Sorting within pivot tables

ramoncova06 — Thu, 07 May 2015 22:45:12 GMT

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)

Re: Sorting within pivot tables

Thu, 07 May 2015 22:55:42 GMT

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.

Re: Sorting within pivot tables

ramoncova06 — Thu, 07 May 2015 23:00:19 GMT

did you add the expression to the products ?

Re: Sorting within pivot tables

Thu, 07 May 2015 23:04:38 GMT

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

Re: Sorting within pivot tables

ramoncova06 — Thu, 07 May 2015 23:10:14 GMT

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

Re: Sorting within pivot tables

Anonymous — Fri, 08 May 2015 01:43:05 GMT

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

Re: Sorting within pivot tables

vikasmahajan — Fri, 08 May 2015 03:50:31 GMT

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

Vikas

Re: Sorting within pivot tables

sunilkumarqv — Fri, 08 May 2015 05:46:29 GMT

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 )