Month sort order

[#1]

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,

[nagaiank]

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.

[#3]

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

[sunny_talwar · Accepted solution]

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

[#5]

Thanks Sunny this works great !

[sunny_talwar]

Awesome

I am glad it is working now.

Best,

Sunny

[#7]

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

Regards,

   Colman