Financial Calendar Development with Hours

Fri, 22 Mar 2013 22:05:37 GMT

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

Sat, 23 Mar 2013 01:02:21 GMT

Have a look at the attached.

Re: Financial Calendar Development with Hours

Wed, 03 Apr 2013 15:08:19 GMT

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

Fri, 05 Apr 2013 15:23:37 GMT

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.

topic Re: Financial Calendar Development with Hours in QlikView

Financial Calendar Development with Hours

Re: Financial Calendar Development with Hours

Re: Financial Calendar Development with Hours

Re: Financial Calendar Development with Hours