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,
Week(Date)
as ISOWeekNo,
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.
