topic Sorting Months from Static Table in QlikView

Sorting Months from Static Table

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

I have the following static table but when I make a chart it sorts the months alphabetically. I tried month() and monthname() and that causes the chart to show nothing, probably because it isnt being stored as a date. Any ideas on being able to sort this by Jan-Dec?

Table1:
LOAD * Inline [
Month, 2015, 2016, 2017
Jan, 2776, 3182, 3884
Feb, 2605, 3140, 3451
Mar, 3379, 3992, 4851
Apr, 3447, 4079, 4899
May, 3706, 4390, 6391
June, 4144, 5023, 6752
July, 4285, 4559, 6144
Aug, 3748, 4901, 6490
Sept, 3753, 4208, 5237
Oct, 3113, 3869, 5528
Nov, 3084, 3861,
Dec, 3398, 3668,
];

*I meant to mention we are moving the information to a database so it can be easily updated without having to move it up to User Acceptance Testing every time. The database reorders everything so sorting by load order will not help in this case.*

Re: Sorting Months from Static Table

krishnacbe — Wed, 15 Nov 2017 14:16:47 GMT

Hi,

Try Sorting using Load Oder -> Original and uncheck all other sort options

Re: Sorting Months from Static Table

greend21 — Wed, 15 Nov 2017 14:20:10 GMT

Actually, that works but I meant to mention we are moving the infromation to a database so it can be easily updated without having to move it up to User Acceptance Testing every time. The database reorders everything so sorting by load order will not help in this case.

Re: Sorting Months from Static Table

avinashelite — Wed, 15 Nov 2017 14:21:20 GMT

Add one more table for sort and use that in expression like this

Go to chart properties > Sort > Expression > Sort_Order

Sort:
LOAD * Inline [
Month,Sort_Order
Jan,1
Feb,2
Mar,3
Apr,4
May,4
June,6
July,7
Aug,8
Sept,9
Oct,10
Nov,11
Dec,12
];

Re: Sorting Months from Static Table

krishnacbe — Wed, 15 Nov 2017 14:23:27 GMT

You can add in your inline table with MonthID as 1, 2.. 12 and use that as Expression -> MonthID()

LOAD * Inline [
MonthID,Month, 2015, 2016, 2017
1, Jan, 2776, 3182, 3884
2, Feb, 2605, 3140, 3451
3, Mar, 3379, 3992, 4851
4,Apr, 3447, 4079, 4899
5May, 3706, 4390, 6391
6,June, 4144, 5023, 6752
7,July, 4285, 4559, 6144
8,Aug, 3748, 4901, 6490
9,Sept, 3753, 4208, 5237
10,Oct, 3113, 3869, 5528
11,Nov, 3084, 3861,
12,Dec, 3398, 3668,
];

Re: Sorting Months from Static Table

antoniotiman — Wed, 15 Nov 2017 14:29:06 GMT

May be like this

SET MonthNames='Jan;feb;mar;apr;may;June;July;aug;sept;oct;nov;dec';
Table1:
CrossTable(Year,Value,2)
LOAD Month(Date#(Month,'MMMM')) as MonthN,* Inline [
Month, 2015, 2016, 2017
Jan, 2776, 3182, 3884
Feb, 2605, 3140, 3451
Mar, 3379, 3992, 4851
Apr, 3447, 4079, 4899
May, 3706, 4390, 6391
June, 4144, 5023, 6752
July, 4285, 4559, 6144
Aug, 3748, 4901, 6490
Sept, 3753, 4208, 5237
Oct, 3113, 3869, 5528
Nov, 3084, 3861,
Dec, 3398, 3668,
];