This sums at partial sum levels but not as a grand total. I've tried to re-arrange the formula to nest the IF statement inside the Sum statement, but this does not work.
Thx
//***Initialising variables for deriving fiscal years
Set vFiscalMonth = 7 ; // 1st month of FiscalYr is July
Set vFiscalDay = 6; // 1st Day of the week (0=Mon, 1=Tue, ... , 6=Sun)
MasterCalendar:
Load distinct
FYear&chr(32)&FiscalMonth as FiscalYrMonth,
*;
Load distinct
FYear&chr(32)&Dual('Q' & Ceil(FMonth/3), Ceil(FMonth/3)) as FiscalYrQtr,
Dual(FYear-1 &'/'& FYear, FYear) as FiscalYear, // Dual fiscal year
Dual(Month, FMonth) as FiscalMonth, // Dual fiscal month
Dual('Q' & Ceil(FMonth/3), Ceil(FMonth/3)) as FiscalQuarter,
Ceil((OrderDate-StartOfFWeekOne+1)/7) as FiscalWeekNo,
*;
Load *,
Year&'-'&Quarter as YrQtr,
Year + If(Month>=$(vFiscalMonth), 1, 0) as FYear, // Numeric fiscal year
Mod(Month-$(vFiscalMonth), 12)+1 as FMonth, // Numeric fiscal month
WeekStart(FiscalYearStart,0,$(vFiscalDay)) as StartOfFWeekOne
;
Load
TempDate AS OrderDate,
TempDate AS Date,
week(TempDate) As Week,
Date(Monthstart(TempDate), 'MM/DD/YYYY') as MonthStartYear,
Date(Monthstart(TempDate), 'MMM-YYYY') as MonthYear,
YearStart(TempDate,0,$(vFiscalMonth)) as FiscalYearStart,
Year(TempDate) as Year,
Month(TempDate) as Month,
Day(TempDate) as Day,
'Q' & ceil(month(TempDate) / 3) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
;
//=== Generate a temp table of dates ===
LOAD
date(mindate + IterNo()) AS TempDate
,maxdate // Used in InYearToDate() above, but not kept
WHILE mindate + IterNo() <= maxdate;
//=== Get min/max dates from Field ===/
LOAD
min(FieldValue('OrderDate', recno()))-1 as mindate,
max(FieldValue('OrderDate', recno())) as maxdate
AUTOGENERATE FieldValueCount('OrderDate');
left join (MasterCalendar)//Integrating Fiscal Period
LOAD * INLINE [
MonthStartYear, FYear, FiscalPeriod
07/01/2006, 2007, 1
08/01/2006, 2007, 2
09/01/2006, 2007, 3
10/01/2006, 2007, 4
11/01/2006, 2007, 5
12/01/2006, 2007, 6
01/01/2007, 2007, 7
02/01/2007, 2007, 8
03/01/2007, 2007, 9
04/01/2007, 2007, 10
05/01/2007, 2007, 11
06/01/2007, 2007, 12
07/01/2007, 2008, 1
08/01/2007, 2008, 2
09/01/2007, 2008, 3
10/01/2007, 2008, 4
11/01/2007, 2008, 5
12/01/2007, 2008, 6
01/01/2008, 2008, 7
02/01/2008, 2008, 8
03/01/2008, 2008, 9
04/01/2008, 2008, 10
05/01/2008, 2008, 11
06/01/2008, 2008, 12
07/01/2008, 2009, 1
08/01/2008, 2009, 2
09/01/2008, 2009, 3
10/01/2008, 2009, 4
11/01/2008, 2009, 5
12/01/2008, 2009, 6
01/01/2009, 2009, 7
02/01/2009, 2009, 8
03/01/2009, 2009, 9
04/01/2009, 2009, 10
05/01/2009, 2009, 11
06/01/2009, 2009, 12
];
AsOf:
LOAD DISTINCT Date as AsOfDate
RESIDENT MasterCalendar
;
LEFT JOIN (AsOf)
LOAD AsOfDate as Date
RESIDENT AsOf
;
INNER JOIN (AsOf)
LOAD *,1 as YTD
RESIDENT AsOf
WHERE year(AsOfDate) = year( Date)
AND AsOfDate >= Date
;
LEFT JOIN (AsOf)
LOAD *
,if(quarterstart(AsOfDate)=quarterstart( Date),1) as QTD
,if(monthstart(AsOfDate)=monthstart( Date),1) as MTD
