topic Month Ordering for Fiscal/Financial Line Chart in QlikView

Month Ordering for Fiscal/Financial Line Chart

darren_dixon — Wed, 27 Mar 2013 11:26:03 GMT

Hi,

My script:

month(ActualStart) As "Month"

In my chart is ordered by calendar date - Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec.

How do i order this for Fiscal/Financial year? - Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec, Jan, Feb, Mar

I've tried using the sort in chart but can't get it to work.

Thanks,

Darren

Re: Month Ordering for Fiscal/Financial Line Chart

jonathandienst — Wed, 27 Mar 2013 11:35:46 GMT

Darren

Create a fiscal month dual field with something like this:

LOAD ...
      ActualStart,
      Month(ActualStart) As Month,
      If(Month(ActualStart) < 4, 
            Dual(''&Month(ActualStart), Month(ActualStart)+8), 
            Dual(''&Month(ActualStart), Month(ActualStart)-4)) As FiscalMonth, 
      If(Month(ActualStart) < 4, Year(ActualStart), Year(ActualStart) + 1) As FiscalYear
      ...
From ....

You would ideally do this in the master calendar script.

Hope that helps

Jonathan

Edit: fixed missing parenthesis

Re: Month Ordering for Fiscal/Financial Line Chart

Wed, 27 Mar 2013 11:37:42 GMT

Fiscal year starts from April ?

try this

month(AddMonths(ActualStart, -4)) as FiscalMonth

Borys