Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
regowins
Creator II
Creator II

Sort date in Month Year format

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!

1 Solution

Accepted Solutions
regowins
Creator II
Creator II
Author

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.

View solution in original post

8 Replies
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

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
regowins
Creator II
Creator II
Author

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.

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Creator II
Creator II
Author

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.

Not applicable

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

bpoluha
Partner - Contributor
Partner - Contributor

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
Contributor
Contributor

This works !!. Thank you so much.