month order on graph

[#1]

Hi,

Please see attached. We need the graph to show results in month order on fiscal year (i.e. April through to March).

As you can see it's not showing in order irrespective of 'sort' option.

Can someone please help?

Chris

[jagan]

Hi Chris,

Create a master calendar and sort the way you want.  Check for sample script in below link

Fiscal and Standard Calendar generation

Regards,

Jagan.

[#3]

hi please review this link.

Fiscal Year

regards Nishit

[Anonymous]

Hi,

Create a master calendar is good idea, but you can also use

pick(Match(Month,'January','February','March','April','May','June','July','August','September','October','November','December'),1,2,3,4,5,6,7,8,9,10,11,12)

to sort your graphic by Month without reload and recreate table in the script.

[#5]

Hi,

Thanks for this but still isn't clear to us.

Do we literally copy and paste the below into our script? We don't understand what the three steps mean:

Set vFM = 4 ;                                                          // First month of fiscal year

Calendar:

Load Dual(fYear-1 &'/'& fYear, fYear) as FYear,          // Dual fiscal year

         Dual(Month, fMonth)                as FMonth,           // Dual fiscal month

          *;

Load Year + If(Month>=$(vFM), 1, 0) as fYear,           // Numeric fiscal year

         Mod(Month-$(vFM), 12)+1        as fMonth,          // Numeric fiscal month

          *;

Load Year(Date)                              as Year,           // Your standard master calendar

         Month(Date)                            as Month,

[perumal_41]

Hi Chris,

Set vFM = 4 ;                                                          // First month of fiscal year

Calendar:

this place applying dual function

Load Dual(fYear-1 &'/'& fYear, fYear) as FYear,          // Dual fiscal year

         Dual(Month, fMonth)                as FMonth,           // Dual fiscal month

          *;

Below script preceding load of previous load script and changing the starting   month based in set Variable input values on vFM  i.e vFM = 4

Load Year + If(Month>=$(vFM), 1, 0) as fYear,           // Numeric fiscal year

         Mod(Month-$(vFM), 12)+1        as fMonth,          // Numeric fiscal month

          *;

Below load statement  your calendar script, and this place starting month will be jan

Load Year(Date)                              as Year,           // Your standard master calendar

         Month(Date)                            as Month,

[#7]

Hi Perumal,

We have a field called 'date of request' which is what were interested in. We need the script above (and referred to in other links) to then populate fical year and month from this field.

What do we have to change in script or load separately for this to work?

Chris

[perumal_41]

Hi chirs,

do you have master calendar in your script ?,

if yes then apply on top of  master calendar .

else use below script

Load Dual(fYear-1 &'/'& fYear, fYear) as FYear,          // Dual fiscal year

         Dual(Month, fMonth)                as FMonth,           // Dual fiscal month

          *;

Load Year + If(Month>=$(vFM), 1, 0) as fYear,           // Numeric fiscal year

         Mod(Month-$(vFM), 12)+1        as fMonth,          // Numeric fiscal month

          *;

LOAD

    Date(Date) AS [date of request],

    Year(Date) AS Year,

    'Q' & Ceil(Month(Date) / 3) AS Quarter,    

    Month(Date) As Month,

    Day(Date) As Day,

    Week(Date) As Week;

Load Date(MinDate + IterNo() -1 ) AS Date  While (MinDate + IterNo() - 1) <= Num(MaxDate);

Load

    Min([date of request]) AS MinDate,

    Max([date of request]) AS MaxDate

RESIDENT table;

[#9]

Hi,

Great. No we don't have a master calender in our script. How do we do this?

Chris

[perumal_41]

Try like below

Load Dual(fYear-1 &'/'& fYear, fYear) as FYear,          // Dual fiscal year

         Dual(Month, fMonth)                as FMonth,           // Dual fiscal month

          *;

Load Year + If(Month>=$(vFM), 1, 0) as fYear,           // Numeric fiscal year

         Mod(Month-$(vFM), 12)+1        as fMonth,          // Numeric fiscal month

          *;

LOAD

    Date(Date) AS [date of request],

    Year(Date) AS Year,

    'Q' & Ceil(Month(Date) / 3) AS Quarter,    

    Month(Date) As Month,

    Day(Date) As Day,

    Week(Date) As Week;

Load Date(MinDate + IterNo() -1 ) AS Date  While (MinDate + IterNo() - 1) <= Num(MaxDate);

Load

    Min([date of request]) AS MinDate,

    Max([date of request]) AS MaxDate

RESIDENT factable ( date fileds contains table name) ;