Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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

Re: Sort date in Month Year format

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 Replies

Re: Sort date in Month Year format

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

Re: Sort date in Month Year format

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

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.

MVP & Luminary
MVP & Luminary

Re: Sort date in Month Year format

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

Re: Sort date in Month Year format

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

Re: Sort date in Month Year format

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

bpoluha
New Contributor

Re: Sort date in Month Year format

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!

Smiley Happy