topic Re: Fiscal Year - Month Sort Order in QlikView

Fiscal Year - Month Sort Order

danielle_v — Fri, 31 Jan 2014 14:44:52 GMT

Hi All,

I am trying to put together a chart to show sales volumes by month for the current and previous year. I am working to a fiscal calendar which runs from May-Apr.

My issue is the sort order of the months, as I obviously want these to match the fiscal year May-Apr. I have tried sorting by the following expression

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

However, this gives me the following result;

Not a clue why this is doing this, and how to get the months to correctly sort from May-Apr!

Any help much appreciated!

Re: Fiscal Year - Month Sort Order

Fri, 31 Jan 2014 14:49:44 GMT

Hi Danielle

You should be able to do it with DUAL function. Just make sure to keep the default sort order

Lukasz

Re: Fiscal Year - Month Sort Order

Anonymous — Fri, 31 Jan 2014 15:22:17 GMT

You can make a calculated dimension.

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

Re: Fiscal Year - Month Sort Order

danielle_v — Fri, 31 Jan 2014 15:30:46 GMT

Hi Lukasz,

Thanks for your reply!

I have just tried your suggestion and this works when no Year selections are made. However, as soon as I select a year from my calendar, the order of the months changes back to the order shown in my original post.

I wasn't aware of the Dual function though, so definitely something that I will be able to make use of!

Re: Fiscal Year - Month Sort Order

danielle_v — Fri, 31 Jan 2014 15:34:00 GMT

Hi Michael,

Thanks for your reply!

Just tried your suggestion too, and still March is showing as the first month.

All very strange, unless there's something I've missed!

Re: Fiscal Year - Month Sort Order

Anonymous — Fri, 31 Jan 2014 19:16:50 GMT

You can do use this method though I use it as my last effort.

------------------------------------------------

// Load a dummy table to establish

// sort order for field "Cal_Month".

MonthSort:

LOAD * INLINE [

Cal_Month

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

Jan

Mar

Apr

]

;

Load All Other Tables;

Drop Table MonthSort;

------------------------------------------------

Then you can sort your chart by Load Order.

Re: Fiscal Year - Month Sort Order

Fri, 31 Jan 2014 19:21:51 GMT

Hi,

Results in the chart are an aggregation, so to sort Cal_Month directly in chart you need an aggregate expression like:

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

Hope this helps