We are implementing a master calendar in the pivot that has Fiscal Month and Fiscal Week included. The pivot itself works with all of the other measures that we are calculating on, but perhaps something needs to be changed in the load script for the data for this particular table?
MasterCalendar:
Load
*,
Date as PostedDate,
Date as ActualShipDate,
Dual(Quarter &'-'& Year, Year & QuarterNumber) as QuarterYear, //Dual used for sorting in charts.
Dual(Month &'-'& Year, Year & Num(Month, '00')) as MonthYear, //Dual used for sorting in charts.
Num(Month) as MonthNum;
Load
TempDate AS Date,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
'Q' & ceil(month(TempDate) / 3) AS Quarter,
ceil(month(TempDate) / 3) AS QuarterNumber,
//Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay,
weekend(TempDate) as WeekEnd; //Used for week ending calculations;
//=== Generate a temp table of dates ===
LOAD
date(mindate + IterNo()) AS TempDate
,maxdate // Used in InYearToDate() above, but not kept
WHILE mindate + IterNo() <= maxdate;
//=== Add a year to maxdate so we have some future dates to select ===/
LOAD
mindate,
Date(AddYears(maxdate, 1)) as maxdate;
//=== Get min/max dates from Field ===/
LOAD
min(FieldValue('Date', recno()))-1 as mindate,
max(FieldValue('Date', recno())) as maxdate
AUTOGENERATE FieldValueCount('Date');
Drop table Link;
Adhoc_temp:
LOAD
ID,
// these fields probably aren't needed
"Start time",
"Completion time",
Email,
Name,
"Last modified time",
// these are
Account as ClientCommonName,
"Date of Adhoc Revenue or Expense" as Date,
"Adhoc Type" as AdhocType,
"Adhoc Expense Amount" as AdhocExpenseAmount,
"Adhoc Revenue Amount" as AdhocRevenueAmount,
Facility,
"Expense Detail" as ExpenseDetail,
"Revenue Detail" as RevenueDetail,
"Expense or Revenue Distribution" as Distribution
FROM [AdhocData.xlsx]
(ooxml, embedded labels, table is Form1);
Adhoc_monthly_temp:
IntervalMatch(Date)
Load Distinct
MonthStart(Date) as PeriodStart,
MonthEnd(Date) as PeriodEnd
Resident Adhoc_temp;
Join(Adhoc_monthly_temp)
LOAD
ID,
// these fields probably aren't needed
"Start time",
"Completion time",
Email,
Name,
"Last modified time",
// these are
ClientCommonName,
AdhocType,
AdhocExpenseAmount,
AdhocRevenueAmount,
Facility,
ExpenseDetail,
RevenueDetail,
Distribution,
MonthStart(Date) as PeriodStart,
MonthEnd(Date) as PeriodEnd
Resident Adhoc_temp
Where Distribution = 'Fiscal Period';
Adhoc_weekly_temp:
IntervalMatch(Date)
Load Distinct
WeekStart(Date) as PeriodStart,
WeekEnd(Date) as PeriodEnd
Resident Adhoc_temp;
Join(Adhoc_weekly_temp)
LOAD
ID,
// these fields probably aren't needed
"Start time",
"Completion time",
Email,
Name,
"Last modified time",
// these are
ClientCommonName,
AdhocType,
AdhocExpenseAmount,
AdhocRevenueAmount,
Facility,
ExpenseDetail,
RevenueDetail,
Distribution,
WeekStart(Date) as PeriodStart,
WeekEnd(Date) as PeriodEnd
Resident Adhoc_temp
Where Distribution = 'Fiscal Week'
And NOT(IsNull("Start time"));
Adhoc_temp2:
NoConcatenate Load
*,
MonthStart(Date) as PeriodStart,
MonthEnd(Date) as PeriodEnd
Resident Adhoc_temp
Where Distribution = 'Specific Date';
Concatenate
Load
*
Resident Adhoc_monthly_temp
Where not match(WeekDay(Date),'Sat','Sun');
Concatenate
Load
*
Resident Adhoc_weekly_temp
Where not match(WeekDay(Date),'Sat','Sun');
Drop table Adhoc_temp;
Drop table Adhoc_monthly_temp;
Drop table Adhoc_weekly_temp;
Qualify *;
Unqualify %ClientCommonName_Date_Key;
Adhoc:
Load
AutoNumber(Facility & '|' & ClientCommonName & '|' & Date) as %ClientCommonName_Date_Key,
*,
if(Distribution='Fiscal Week',AdhocExpenseAmount/5,if(Distribution = 'Fiscal Period', AdhocExpenseAmount/NetWorkDays(PeriodStart, PeriodEnd), AdhocExpenseAmount)) as DailyExpense,
if(Distribution='Fiscal Week',AdhocRevenueAmount/5,if(Distribution = 'Fiscal Period', AdhocRevenueAmount/NetWorkDays(PeriodStart, PeriodEnd), AdhocRevenueAmount)) as DailyRevenue
Resident Adhoc_temp2
//where ID > 7 // update to exclude test records
;
Unqualify *;
drop Table Adhoc_temp2;
