topic Re: Month sort order in QlikView

Month sort order

Thu, 01 Oct 2015 15:07:52 GMT

I have the following field with associated mapping. Basically loading fiscal periods ie 1-12 then applying the mapping to show 1 as April, 2 as May and so on ... However the sort order will not show correctly ie from April - March. The months just keep getting jumbled. Do I need a dual function here or is there a simpler solution ?

MonthMap:

Mapping LOAD * INLINE [

PERIOD_NUM, Month

1, Apr

2, May

3, Jun

4, Jul

5, Aug

6, Sep

7, Oct

8, Nov

9, Dec

10, Jan

11, Feb

12, Mar

];

ApplyMap('MonthMap', If(Month($(vDateField)) > 3, Month($(vDateField)) - 3, Month($(vDateField)) + 9)) as FMonth,

Re: Month sort order

nagaiank — Thu, 01 Oct 2015 15:55:29 GMT

In your calendar, add two fields having Fiscal Year and Fiscal Period Number.

For Jan 2015, calendar year/month is 2015/1, but Fiscal Year/Period is 2014/10

Use Fiscal Period number as dimension, numerically sorted ascending.

Re: Month sort order

Thu, 01 Oct 2015 15:58:04 GMT

Im not sure if Ive understood you here. I need this to display as Apr, May, Jun .... rather than period numbers

Re: Month sort order

sunny_talwar — Thu, 01 Oct 2015 15:58:37 GMT

I think Dual might be help better:

Dual(Month(DateField), If(Month(DateField) <= 9, Num(Month(DateField)) + 3, Num(Month(DateField)) - 9))

Dual(Month(DateField), If(Month(DateField) > 3, Num(Month(DateField)) - 3, Num(Month(DateField)) + 9))

Re: Month sort order

Thu, 01 Oct 2015 17:09:31 GMT

Thanks Sunny this works great !

Re: Month sort order

sunny_talwar — Thu, 01 Oct 2015 17:43:14 GMT

Awesome

I am glad it is working now.

Best,

Sunny

Re: Month sort order

Fri, 02 Oct 2015 02:28:00 GMT

I'm new to Qlikview. I'm wondering if autonumber could be used with the list of months as a simpler approach.

Regards,

Colman