Sort date in Month Year format

[regowins]

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!

[MayilVahanan]

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

[regowins]

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

[MayilVahanan]

Hi

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.

[rwunderlich]

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

[regowins · Accepted solution]

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.

[#7]

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

[bpoluha]

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!

🙂

[QlikPankaj]

This works !!. Thank you so much.