Hi All,
I have read through many links on how to create a Master Calendar and Single Fact Table. I have tried different options and so far things are not working. What I am trying to accomplish is Fiscal Year Master Calendar using two different date fields. The field names are RequestDate and StartDate. They are in two different tables. I have copied the script below and any assistance would be helpful:
//Energy Data Table//
UDEnergyBillData:
LOAD DudeAccountNumber,
Building,
Account,
AccountProvider,
AccountUtilityType,
AccountUOM,
Period,
Use,
Amount,
StartDate,
EndDate;
SQL SELECT *
FROM UDEnergyBillData;
//Work Order Data Table//
MDWorkOrder:
LOAD DudeAccountNumber,
WorkOrderNumber,
Status,
Priority,
RequestDate,
LocationName,
BuildingName,
ActualCosts,
ActualHours,
Purpose,
Craft,
Project,
Classification,
Type,
PMScheduleID;
SQL SELECT *
MDWorkOrder;
//Fact Table to Combine dates into one table//
FactTable:
Load DudeAccountNumber,
Makedate(year(StartDate),month(StartDate),day(StartDate)) as Date,
'Start' as Link
Resident UDEnergyBillData;
Concatenate(FactTable)
Load DudeAccountNumber,
Makedate(year(RequestDate),month(RequestDate),day(RequestDate)) as Date,
'Request' as Link
Resident MDWorkOrder;
//Fiscal Year Master Calendar//
| Set vFM = 7 ; | // First month of fiscal year |
| Set vFD = 6; | // First Day of the week (0=Mon, 1=Tue, ... , 6=Sun) |
MasterCalendar:
Load distinct
Dual(fYear-1 &'/'& fYear, fYear) as FYear, // Dual fiscal year
| Dual(Month, fMonth) | as FMonth, // Dual fiscal month |
Dual('Q' & Ceil(fMonth/3), Ceil(fMonth/3)) as FQuarter,
Ceil((Date-StartOfFWeekOne+1)/7) as FWeekNo,
*;
Load Year + If(Month>=$(vFM), 1, 0) as fYear, // Numeric fiscal year
| Mod(Month-$(vFM), 12)+1 | as fMonth, // Numeric fiscal month |
Dual('Q' & Ceil(Month/3), Ceil(Month/3)) as Quarter,
| WeekStart(Date,0,$(vFD)) | as StartOfFWeekOne, |
*;
Load
Date,
YearStart(Date,0,$(vFM)) as FYearStart,
| Year(Date) | as Year, |
| Month(Date) | as Month, |
Date(Monthstart(Date), 'MMM-YYYY') as MonthYear,
Dual(WeekDay(Date),Mod(WeekDay(Date-$(vFD)),7)+1) as WeekDay,
| Day(Date) | as Day |
| // Date(BillDate'MM/DD') | as DATEMMDD |
Resident FactTable;
Exit Script;
I do not get any error messages or synthetic keys but when I create list box on my MainDashboard page the date fields dont change the data and then month list box is not in Fiscal year order. The Master Calendar code I am using above seems to work if I have one date field so I am thinking it is with my Fact Table logic. Let me know if there is anything else you need.
Thanks in advance
David
