Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys,
The financial calendar 2011/ 2012 starts from 01.10.2011 at 6 Hrs and ends on 01.10.2012 at 6 Hrs. The calendar must be always with Hours starting from 6 hrs in the morning to 5 everyday
Can any body help me create the calendar? I am getting from 01.10.2011 6 Hrs to 31.09.2011. But I need until 01.10.2012 6 Hrs.
Attached is my Application
Have a look at the attached.
Have a look at the attached.
Hi Donald,
one question. Instead of using Tempdate including Year, Month, Day, Hour is it possible to produce financial year using Calendaryear, Calendarmonth, CalendarDay, Calendarhour?
Struggling to get it...
Thanks in advance
Sravan
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.