topic Re: how to sort month by order? in QlikView

how to sort month by order?

Wed, 26 Aug 2015 09:50:50 GMT

hi to all,

Anyone of you explain how to sort the month by order.

My month dimension like below

month:

Apr

Aug

Jul

Jun

Sep

Oct

Nov

Mar

May

Jan

Feb

But I want month list like

Month

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Nov

Dec

Re: how to sort month by order?

ecolomer — Wed, 26 Aug 2015 09:54:11 GMT

if the field have a date format, order are automaticly good as Num(Month)

Can you upload more explicity your problem?

To sort by Month (numeric) is necesary to have date format

Re: how to sort month by order?

jonathandienst — Wed, 26 Aug 2015 09:57:35 GMT

That's because your month is a text field. Convert it to a proper month field during load. The month field is a dual with the month number as a value and a text representation of the 3 character month name.

LOAD...

Month As MonthText, // do you need this field?

Month(Date#(Month & '2015', 'MMMYYYY')) As Month,

(the year used is irrelevant)

This month field should be sorted numerically (as is usual for dual values)

Re: how to sort month by order?

tresB — Wed, 26 Aug 2015 09:58:00 GMT

Sort expression: =Month(Date#(month,'MMM'))

PFA

Re: how to sort month by order?

prajapatiamar38 — Wed, 26 Aug 2015 10:18:24 GMT

Hi try this

LOAD * INLINE [

Month,Month_NO

Apr,4

Aug,8

Jul,7

Jun,6

Sep,9

Oct,10

Nov,11

Dec,12

Mar,3

May,5

Jan,1

Feb,2

];

Then in list box of Month Go to Sort Tab>Expression write the Month_NO

Thanks And Regards

Amarnath

Re: how to sort month by order?

krishna20 — Wed, 26 Aug 2015 10:32:35 GMT

Hi,

Please find the attachment.

Edit

month:

LOAD Month(Date#(Month,'MMM')) as month

INLINE [

Month,

Apr

Aug

Jul

Jun

Sep

Oct

Nov

Mar

May

Jan

Feb

];

Re: how to sort month by order?

MarcoWedel — Wed, 26 Aug 2015 11:04:33 GMT

Hi,

for the

Month(Date#(month,'MMM'))

approach (best done in the script) you might have to adjust your MonthNames variable to

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

if that's not your local default.

regards

Marco

Re: how to sort month by order?

sunilkumarqv — Wed, 26 Aug 2015 11:26:15 GMT

Copy the script and use Sortvalue descending in sort expression.

Load *, INLINE [

Month,Sortvalue

Apr,4

Aug,8

Jul,7

Jun,6

Sep,9

Oct,10

Nov,11

Dec,12

Mar,3

May,5

Jan,1

Feb,2

];

Re: how to sort month by order?

kavita25 — Wed, 26 Aug 2015 11:44:12 GMT

In Sort Tab, under expression:

wildmatch(Month_field,'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec')

Re: how to sort month by order?

mohammadkhatimi — Wed, 26 Aug 2015 11:47:29 GMT

=wildmatch(capitalize(only({1}CalMonth)),'JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC')

Hope this will helps you..!!

Regards,

Mohammad

Re: how to sort month by order?

Fri, 30 Oct 2015 08:01:28 GMT

Thanks works

Re: how to sort month by order?

Thu, 05 Nov 2015 09:11:55 GMT

Hi Mohammed

I did that but is there a reason why if i select months the order jumps around. how do i keep the order as jan to Dec irrespective of the month selected

Re: how to sort month by order?

mohammadkhatimi — Fri, 06 Nov 2015 03:50:27 GMT

In which chart you are applying???

Re: how to sort month by order?

kavita25 — Fri, 06 Nov 2015 05:17:24 GMT

Try this Expression:

wildmatch(only({1}Month_field),'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec')

only({1})- with this it wont change the order.

Re: how to sort month by order?

sasikanth — Fri, 06 Nov 2015 06:02:54 GMT

HI,

Uncheck State Check box , if you want to keep selected values as it is,

Re: how to sort month by order?

Fri, 06 Nov 2015 06:12:15 GMT

Brilliant Thank you

Re: how to sort month by order?

Fri, 06 Nov 2015 06:18:04 GMT

Thanks its working

Re: how to sort month by order?

Mon, 18 Jul 2016 20:49:42 GMT

Excellent!! It worked flawlessly!!

It´s good to say that you must put that expression in the Sort tab of Properties.

Re: how to sort month by order?

chandu441 — Wed, 22 Feb 2017 07:46:23 GMT

Thanks for your expression which helps for my case. After 3 hours of time waste I came to your suggestion.

Re: how to sort month by order?

Anonymous — Tue, 24 Apr 2018 00:16:58 GMT

It worked very well for me, but I couldn't understand why Month(MonthAbbr) do not work and Month(Date#(MonthAbbr, 'MMM')) works.

MonthAbbr being the abbreviation for the months names.