topic Re: Need help on sorting Year-Month group. in QlikView

Need help on sorting Year-Month group.

Wed, 15 Jan 2014 15:45:55 GMT

Hi, everyone -

I'm quite new to QlikView. This might be very basic, but here I go:

I have created a Year-Month group, which, indeed, looks like 'Dec-2006', 'Feb-2008'... 'Nov-2013', etc. I am using it as a dimension in my chart object. Thing is, I want it to be shown ordered ('Jan-2006', 'Feb-2006' ... 'Dec-2013').

How can this be achieved?

Thank you all in advance.

Martín.

Re: Need help on sorting Year-Month group.

Wed, 15 Jan 2014 15:47:13 GMT

In the Sort tab of your chart check Numeric Value and select Ascending from the dropdown for your Year-Month dimension.

Re: Need help on sorting Year-Month group.

Clever_Anjos — Wed, 15 Jan 2014 15:54:37 GMT

Is your field a string?

If so use date#(yourfield,'MMM-YYYY') as your sorting expression

Re: Need help on sorting Year-Month group.

Wed, 15 Jan 2014 15:58:12 GMT

Thanks for your answer, Christian, but I'm afraid I'll have to strike that suggestion. That would work if I had Year, solely, but I have fields like 'Dec-2006', strictly.

Thanks again : )

Re: Need help on sorting Year-Month group.

Wed, 15 Jan 2014 16:06:45 GMT

Hi, Anjos. It is the result of:

Year & '-' & Month

where Year looks like '2006', '2007'..., and Month looks like 'Jan', 'Feb', 'Mar'.

Thanks for your answer.

Re: Need help on sorting Year-Month group.

Wed, 15 Jan 2014 16:07:09 GMT

No problem, I was assuming that your date field was loaded in with an interpreter function. Just an FYI mine works if the date is interpreted in the load script.

Date:

LOAD * INLINE

[

Date

Dec-2006,

Feb-2008,

Nov-2013

];

DateFinal:

LOAD

date#(Date,'MMM-YYYY')as FinalDate

RESIDENT Date;

DROP TABLE Date;

But if it is loaded in without being interpreted it may be easier to just sort by expression as Clever Anjos suggested.

Re: Need help on sorting Year-Month group.

Wed, 15 Jan 2014 16:16:08 GMT

What you say is relly good to know. Thanks for the backup, Christian!

Re: Need help on sorting Year-Month group.

Wed, 15 Jan 2014 16:19:56 GMT

Oh, terrific. I tweaked your example just a bit and it worked! : )

I don't know why, but date#('Year' & '-' & 'Month','YYYY-MMM') does not seem to work.

I defined an INLINE table mapping 'Month' ('Jan', 'Feb'...) to ''MonthNumber', and then I sorted as you suggested date#('Year' & '-' & 'MonthNumber','YYYY-MM') and it worked.

Thanks a lot, Clever Anjos.

Re: Need help on sorting Year-Month group.

Clever_Anjos — Wed, 15 Jan 2014 16:39:01 GMT

If you do

DATE(date#(Year & '-' & Month,'MMM-YYYY'),'MMM-YYYY') at script level, you don´t need to worry about resorting into your graph