Month Ordering for Fiscal/Financial Line Chart

[darren_dixon]

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

[jonathandienst]

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

[#3]

Fiscal year starts from April ?

try this

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

Borys