3 Replies Latest reply: Apr 5, 2013 11:23 AM by Donald Hutchins RSS

    Financial Calendar Development with Hours

    Sravan Puppala

      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

          • Re: Financial Calendar Development with Hours
            Sravan Puppala

            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

              • Re: Financial Calendar Development with Hours

                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.