Pivot table sort order

[Anonymous]

Hi!

I have made a pivot table where Month dimension is sorted using Month_sort dimension.

LOAD Month(Date#(1&Month, 'YMMM')) as Month,
Month_sort;
LOAD * Inline [
Month, Month_sort
Jan, 7
Feb, 8
Mar, 9
Apr, 10
May, 11
Jun, 12
Jul, 1
Aug, 2
Sep, 3
Oct, 4
Nov, 5
Dec, 6
];

I should mention that I am using an expresion with a modification of sum({1} 1) in one of my expressions so that the table does not collapse when using filters (all rows should be seen at all times). However when I am using Month filter the selected month "moves" to the most right of the table and the sorting does not seem to be working anymore. How can I fix it?

Thank you!

[sunny_talwar]

Are you using an expression on the sort tab? May be you need to add {1} in set analysis of the sort expression also

[its_anandrjs]

In a sort order properties you can try

Only( {1} Match(Month_sort,7,8,9,10,11,12,1,2,3,4,5,6) )

[Anonymous]

I am using this: =Month_sort

Can you explain how to use {1}?

[Anonymous]

Tried your suggestion. Month order in the table changes to Jan, Feb, Mar... without filters.

Is the syntax correct?

[sunny_talwar · Accepted solution]

Try this

Only({1} Month_sort)

[Frank_Hartmann]

BTW,

with following kind of Statement you can make your sortorder dynamic and wont need to manually adjust it every month:

LOAD
Month(Date#(Month, 'MMM')) as Month,
Mod(Month(Date#(Month, 'MMM'))-(Month(Today())-2),12)+1 as Sortorder;

LOAD * Inline [
Month,
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
];

[its_anandrjs]

Yes it seems

Only( {1} Match(Month_sort,'7','8','9','10','11','12','1','2','3','4','5','6') )

Or

//This works perfect

Only( {1} Month_sort )