topic Re: Pivot table sort order in QlikView

Pivot table sort order

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Pivot table sort order

sunny_talwar — Tue, 26 Sep 2017 14:02:09 GMT

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

Re: Pivot table sort order

its_anandrjs — Tue, 26 Sep 2017 14:06:52 GMT

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

Re: Pivot table sort order

Anonymous — Tue, 26 Sep 2017 14:07:08 GMT

I am using this: =Month_sort

Can you explain how to use {1}?

Re: Pivot table sort order

Anonymous — Tue, 26 Sep 2017 14:16:07 GMT

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

Is the syntax correct?

Re: Pivot table sort order

sunny_talwar — Tue, 26 Sep 2017 14:16:28 GMT

Try this

Only({1} Month_sort)

Re: Pivot table sort order

Frank_Hartmann — Tue, 26 Sep 2017 14:25:03 GMT

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
];

Re: Pivot table sort order

its_anandrjs — Tue, 26 Sep 2017 17:02:33 GMT

Yes it seems

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

//This works perfect

Only( {1} Month_sort )