Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi Chris,
Two things you have to do :
1. In your data the month names are not consistent. you have month name Jan and rest all Month names with full abbreviations. Make sure you correct that to show full month name.
2. Second add this inline table in your script.
LOAD * INLINE [
Month,sortID
April,1
May,2
June,3
July, 4
August,5
September,6
October,7
November,8
December,9
January,10
February,11
March, 12
];
Now reload the app. and then in the sort order of the chart .. check option for expression then given sortid as your expression. Please see attached app.
Please let us know if that helps
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.
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.
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,
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,
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
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;
Hi,
Great. No we don't have a master calender in our script. How do we do this?
Chris
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) ;