I came up with an easy way to take the monthly budget number and blow it out to a daily figure. The key to the approach was using my corporate calendar to determine how much of the monthly budget or salary needed to be applied to each day. I could have just done some simple division by the number of days in the month but this would have meant we were expecting everyone to work on weekends and holidays and the daily budget would not have been in line with the real expectations for billable time.
The first thing I am going to do is load a table containing all the holidays for the company. there are three fields, the date, a description and an value for how much of the day is actually a holiday. Our company has a policy of offering half day holidays on Christmas Eve and New Year's Eve so I have to account for that in my calculations.
Holidays:
Load * INLINE [
HolidayDate,HolidayDesc,HolidayValue
1/1/2009,New Year's Day,0
5/25/2009,Memorial Day,0
7/3/2009,Independence Day Floating Holiday Placeholder,0
9/7/2009,Labor Day,0
11/26/2009,Thanksgiving,0
11/27/2009,Day After Thanksgiving,0
12/24/2009,Christmas Eve,0.5
12/25/2009,Christmas Day,0
12/31/2009,New Year's Eve,0.5
1/1/2010,New Year's Day,0
5/31/2010,Memorial Day,0
7/5/2010,Independence Day Floating Holiday Placeholder,0
9/6/2010,Labor Day,0
11/25/2010,Thanksgiving,0
11/26/2010,Day After Thanksgiving,0
12/24/2010,Christmas Eve,0.5
12/27/2010,Christmas Day Floating Holiday Placeholder,0
12/31/2010,New Year's Eve,0.5
];
The next step is to concatenate all my holiday dates into one string, I will use this string as a parameter in the NetWorkDays function which will count the number of working days in a range of dates. It uses a comma separated list to define holidays to skip along with the weekends.
The Concat function is an aggregate function so I cannot just assign it to a variable I have to use a load statement to use the aggregate function. There may be other ways to do this with the aggr() function but this is the approach that worked for me.
HolidayCalendar:
Load
Chr(39) & Replace(Concat(HolidayDate,','),',',chr(39) & ',' & chr(39)) & chr(39) as HolidayList
resident Holidays;
Let vHolidayList = Peek('HolidayList',0,'HolidayCalendar');
The next step is to build the Calendar table. If you have taken the developer course then you have undoubtedly seen this code before.
//Define the range of dates you want to include
Let vMinDate = num(Date('01/01/2009','MM/DD/YYYY'));
Let vMaxDate = Num(Date(Today(),'MM/DD/YYYY'));
Let vToday = num(Today());
//Autogenerate a source table for your calendar
TempCalendar:
Load
$(vMinDate) + RowNo() - 1 as Num,
Date($(vMinDate) + RowNo() - 1) as TempDate
Autogenerate
$(vMaxDate) - $(vMinDate) + 1;
Generally speaking the next step is pretty standard stuff, I am loadind all the different date dimensions I intend to use in my dashboard and an additional field called IsWorkingDay this field (1 for true 0 for false).
MasterCalendarTemp:
Load
TempDate as OrderDate,
Week(TempDate) as Week,
Year(TempDate) as Year,
Month(TempDate) as Month,
Day(TempDate) as Day,
WeekDay(TempDate) as WeekDay,
'Q' & ceil(Month(TempDate)/3) as Quarter,
Date(monthstart(TempDate),'MMM-YYYY') as MonthYear,
Week(TempDate) & '-' & Year(TempDate) as WeekYear,
inyeartodate(TempDate,$(vToday),0) * -1 as CurYTDFlag,
inyeartodate(TempDate,$(vToday),-1) * -1 as LastYTDFlag,
NetworkDays(TempDate,TempDate,$(vHolidayList)) as IsWorkingDay
Resident TempCalendar
order by TempDate ASC;
Next I am going to join my holiday table to the calendar
Left Join (MasterCalendarTemp)
Load
HolidayDate as OrderDate,
HolidayDesc,
HolidayValue
Resident
Holidays;
In this step I am going to create a field that gives me the appropriate value for the working days and those half day holidays.
MasterCalendar:
Load
OrderDate,
Week,
Year,
Month,
Day,
WeekDay,
Quarter,
MonthYear,
WeekYear,
CurYTDFlag,
LastYTDFlag,
IsWorkingDay,
HolidayDesc,
IF(isnull(HolidayValue),IsWorkingDay,HolidayValue) as WorkDayValue
Resident
MasterCalendarTemp;
Finally adding some additional fields so I can easily reference the number of working days in a month or year and cleaning up my temp tables.
Left join (MasterCalendar)
Load
Year,
Sum(WorkDayValue) as YearWorkingDays
Resident
MasterCalendar
Group By
Year;
Left Join (MasterCalendar)
Load
MonthYear,
Sum(WorkDayValue) as MonthWorkingDays
Resident
MasterCalendar
Group By
MonthYear;
Now when I load in my budget and salary tables I can use the MonthWorkingDays field to caclutate the appropriate daily budget and salary amount. I hope you find this post helpful, please comment if you find something that could be done better or if I left something out.
