Hello all.
I am working on a prototype of a dashboard and I wanted to load a master calendar to help with the time dimension.
I got this form an QlikView demo and found a modification to adapt to our fiscal quarters (Q1 = Oct - Dec).
What I would like to do is be able to provide dimensions using more natural language such as "This week", "Last Week", "This Month", "Last Month", Etc.. for Q and year.
I wanted to provide expressions to evaluate week over week, month over month, etc..
I made an alteration to the master calendar script to get a This Week and Last Week using an If statement. But it only worked where the date matched the week ending and for the previous week I had to subtract 7 to get the right date.
It did work but I'm concerned this will not be an efficient method to write in these dimensions for all the weeks.
The data is structured by week ending (which is always a saturday) beginning in May of 17.
Any help is appreciated.
Thanks!
Nate
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & if (Ceil (rowno()/3) = 4, 1, Ceil (rowno()/3) + 1) as Quarter
AUTOGENERATE (12);
Temp:
Load
min(weekEnding) as minDate,
max(weekEnding) as maxDate
Resident driverMetrics;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
MasterCalendar:
Load
TempDate AS weekEnding,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
YeartoDate(TempDate)*-1 as CurYTDFlag,
YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay,
if(TempDate=Date($(varMaxDate)), 'w0', if(TempDate=Date($(varMaxDate)-7), 'w-1')) as testWeek
//Month(AddMonths(TempDate,-1)) as prevMonth
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
