My sympathies, you have been given an unfair task, I hope your boss has reasonable expectations.
Your QVW is pretty big and I have not been able to download it yet, browser keeps stopping. but I can help with your questions without knowing your data.
First Here is some code for a master calendar, I am keying on a field called order date but you can use whatver works from your data.
//Establish the Range of Dates in your data
DateRange:
Load
Min([Order Order Date]) as MinDate,
Max([Order Order Date]) as MaxDate
Resident
SalesOrderHeader;
//Assign the dates to some variables that we'll use to caclulate the calendar
Let vToday = Peek('MaxDate', 0, 'DateRange');
Let vDataStart = Peek('MinDate', 0, 'DateRange');
//this table gives us all the dates and saves us a bunch of code by being able to
//refer to simply TempDate in the next step
TempCalendar:
Load
$(vDataStart) + RowNo() - 1 as Num,
Date($(vDataStart) + RowNo() - 1) as TempDate
Autogenerate
num($(vToday)) - num($(vDataStart)) + 1;
// This is the master Calendar, it is a very simple calendar but if you
//search the date functions in help you'll be able to add a wide range of
// different time dimensions
MasterCalendar:
Load
TempDate as [Order Order Date],
Month(TempDate) as CalendarMonth,
MonthStart(TempDate) as MonthStart,
MonthEnd(TempDate) as MonthEnd,
Year(TempDate) as Year,
'Q' & ceil(Month(TempDate)/3) as FiscalQuarter
resident TempCalendar;
//Clean up after yourself
Drop Table TempCalendar;
Drop Table DateRange;
I'll post another reply with the MTD and PMTD syntax.
