You could do this in a preceding LOAD this is just before your main load (for Date_NUM, Hour, Day, etc.) and just after the first load (for HoursForDisplay, etc.). Then just change all the references in the FinancialYear calculation formula to refer to Calendarmonth, Calendarday, etc. Something like this:
Calendar:
load
*,
dual(num(Hour) & ' - ' & num(Hour+1),num(Calendarhour)) as HourforDisplay,
dual(num(Calendarhour) & ' - ' & num(Calendarhour+1),num(Calendarhour)) as CalendarHourforDisplay;
load
*,
If( num(Calendarmonth) = 10,
if( Calendarday = 1,
if( Hour > 5,
'FY ' & Right(CalendarYear,2) &'/'& Right(CalendarYear+1,2),
'FY ' & Right(CalendarYear-1,2) &'/'& Right(CalendarYear,2)
),
'FY ' & Right(CalendarYear,2) &'/'& Right(CalendarYear+1,2)
),
if( num(Calendarmonth) > 10,
'FY ' & Right(CalendarYear,2) &'/'& Right(CalendarYear+1,2),
'FY ' & Right(CalendarYear-1,2) &'/'& Right(CalendarYear,2)
)
) As FinancialYear;
load
Date(TempDate,'YYYYMMDD')& Hour as Date_NUM, //DateHour,
Hour,
num(Day(TempDate),'00') as Day,
Month(TempDate) as Month,
'Q' & Ceil(Month(TempDate)/3) as Quartal,
Year(TempDate) as Year,
//###NormalCalenderdata###
Year( ConvertToLocalTime(Year(TempDate) &'-'& num(Month(TempDate),'00') &'-'& num(Day(TempDate),'00') &' '& maketime(num#(Hour)),'UTC-06:00',0)) as CalendarYear,
Month( ConvertToLocalTime(Year(TempDate) &'-'& num(Month(TempDate),'00') &'-'& num(Day(TempDate),'00') &' '& maketime(num#(Hour)),'UTC-06:00',0)) as Calendarmonth,
Day( ConvertToLocalTime(Year(TempDate) &'-'& num(Month(TempDate),'00') &'-'& num(Day(TempDate),'00') &' '& maketime(num#(Hour)),'UTC-06:00',0)) as Calendarday,
Hour( ConvertToLocalTime(Year(TempDate) &'-'& num(Month(TempDate),'00') &'-'& num(Day(TempDate),'00') &' '& maketime(num#(Hour)),'UTC-06:00',0)) as Calendarhour
resident TempCalendar
order by TempDate ASC;
That said, if you do just this, your result will be a bit off because you are converting Calendarmonth, etc. to local time based on a 6 hour GMT offset. But other references, including the HOUR, are not localized like this. If you want to convert to a local time, then perhaps you should consider converting in the beginning, so that all references are consistent. Examine your table for example to see the differnces between your Date_NUM values and the corresponding CalendarXXX fields.
So if the Financial/Fiscal date calc is to be localized using ConvertToLocalTime, then do this in the beginning, something like this:
FOR i = 0 to 23 // Hour 0 to 23
TempCalendar:
LOAD
ConvertToLocalTime(date($(qDateMin)+ IterNo() + MakeTime($(i))),'UTC-06:00') as TempDate
AUTOGENERATE 1
WHILE $(qDateMin) + IterNo() <= $(qDateMax);
NEXT i
Then, you could calculate 'Hour' as Hour(TempDate). And this would be consistent as the localized hour.
