topic Sorting the dates by financial year in QlikView

Sorting the dates by financial year

Fri, 03 Jun 2011 10:41:55 GMT

Hello Community,

I have a posting Year field that starts in Aug and ends in Jul however the sorting order is Jan to Dec, is there a way I can change this and where would I change it? in the expression properties or the main scripting area.

Thanks in Advance

Re: Sorting the dates by financial year

Fri, 03 Jun 2011 11:05:46 GMT

Hi,

you can set sorting to be an expression and use match function. The in the following example the match will return 1 if month=Aug, 2 if month=Sep etc.

match(Month, 'Aug','Sep','Oct','Nov','Dec','Jan','Feb','Mar','Apr','May','Jun','Jul')

Hope this helps!

Stefan

Sorting the dates by financial year

Fri, 03 Jun 2011 11:25:44 GMT

Perfect! Thanks a bunch Stefan, Highly Appreciated!

Re: Sorting the dates by financial year

fidaiazeem — Fri, 03 Jun 2011 11:37:41 GMT

Hi Airpartner,

U can also add a field in the calender table like

If(Num(Month(TempDate))>=8 AND Num(Month(TempDate))<=12,(Num(Month(TempDate))-7),(Num(Month(TempDate))+5)) AS MonthNumber

After Reload, go in Sort expression and sort by MonthNumber.

This field will help u in other charts also..

Hope this helps..

Regards,

Azeem