Specific sort order in multidimensional Pivot Table

[iliyansomlev]

Dear all,

Could you take time to look at the following application. I have 2 vertical and 1 horizontal dimensions and I want to order my pivot table by the value of the first value of the horizontal dimension (KPI1). By writing the sort expression the first dimesnion is ordered correctly but not the second - you can see that for StoreID =1 Dec should be before Jan (as 56>51) but it is not!

Similarly if I want to order by the values in KPI2 or KPI3.

How can I make this sort order correct? I have to use a pivot as my app is much more complicated

[rustyfishbones]

Do you need it like this

[Gysbert_Wassenaar]

If I understand want you want then what you want is not possible. The months will have the same sorting for every store. It's not possible to have Nov-Dec-Jan for StoreID1 and Dec-Jan-Nov for StoreID2.

[iliyansomlev]

Yes , this is what I need - for each store to have different order in the Month dimension, they should be ordered top to bottom according to the KPI1 value.

[jonasheisterkam]

Hi,

here is a solution. But dont use it with big data.

[whiteline]

Hi.

Actually it's almost possible. QV always sorts the distinct dimension values not the 'appearances' in each sublevel.

The trick is to make them different for sorting.

The easiest way is to use straight table and add calculated dimension =aggr(StoreID&Month,  StoreID, Month)

Then you can sort by StoreID and then in that dimension by the KPI1 value. Of course you have to make that column almost invisible.

If the pivot is a strong requirement, you have to modify the data model to add the field that is look like Month, but differs for each StoreID-Month combination. The dual() function can be useful for that. Then just use it instead of Month.

Don't forget to make the pivot read-only to not frustrate the user if he accidentally makes the selection in that field.