topic Re: Sort date in Month Year format in QlikView

Sort date in Month Year format

regowins — Mon, 11 Mar 2013 23:51:25 GMT

Hi,

I have a date field which is in month year format ( Apr-2010). I am not able to sort this field in a bar chart from current to oldest date. I am using a cycle time dimension to show data by year, quarter, & month-year. I have a year and a month field that I can use as well. Thanks!

Re: Sort date in Month Year format

MayilVahanan — Tue, 12 Mar 2013 00:06:29 GMT

hi,

You can sort this by using Dual Function in backend.

Change the date field in num format and name it as Example "DateNum".Suppose ur dimension field having values as Mar 2012, Apr 2012 is "[Month Year]".

thn u have to write Dual([Month Year],DateNum). This will sort ur [Month Year] field.

Hope it helps

Re: Sort date in Month Year format

regowins — Tue, 12 Mar 2013 00:19:43 GMT

Hi, not sure I fully understand. In the script I am creating Month-Year but doing Month &'-'& Year as Month_Year.

Re: Sort date in Month Year format

MayilVahanan — Tue, 12 Mar 2013 00:45:24 GMT

Try like this

ex:

LOAD *,Dual(Month(datefield) & '-'&Year(datefield),num(datefield)) as monthyear;

LOAD *, Date(Date#(date,'MM/DD/YYYY'),'MM/DD/YYYY') as datefield;

LOAD * Inline

[

date

03/12/2012

01/12/2012

05/03/2012

];

hope its helpful to understand.

Re: Sort date in Month Year format

rwunderlich — Tue, 12 Mar 2013 03:57:02 GMT

Create your Month-Year field differently to make it a proper date, then it will sort correctly. If you are extracting from a date field, do it like this:

date(MonthStart(datefield), 'MMM-YYYY') as [Month-Year]

If you just have Month and Year fields available, do it like this:

MakeDate(Year,Month) as [Month-Year]

-Rob

http://robwunderlich.com

Re: Sort date in Month Year format

regowins — Tue, 12 Mar 2013 13:36:31 GMT

Thank you both. The suggestions are very helpful and I will try them out to see which works best. However, I realized that I could use the date# to interpret my Month-Year field as a date and that seems to work.
Ideally, I think it is better to have it in the script so I will look at the suggests you provided.

Re: Sort date in Month Year format

Fri, 19 Apr 2013 17:32:51 GMT

Robs formula worked in the dimension field: date(MonthStart(datefield), 'MMM-YYYY')

Re: Sort date in Month Year format

bpoluha — Fri, 18 Jan 2019 19:10:08 GMT

Hi! Try to do this..

LOAD
Year(DATEFIELD) as DataYear,
Month (DATEFIELD) as DataMonth,
FROM .....

Then go to the Chart that you want to sort -> open Chart Properties -> Expression

Write this:

MakeDate(DataYear, DataMonth)

Click on Apply.

It's ready!

🙂

Re: Sort date in Month Year format

QlikPankaj — Fri, 28 Feb 2020 15:05:34 GMT

This works !!. Thank you so much.