Hi Mike,
To generate missing dates you will have to use a Master Calendar. You can use the script below:
For all days from today minus 7, you can use a calculated dimension:
IF(<YourDateField> <= Date(Today()-7),<YourDateField>)
Master Calendar Script:
minmax_temp:
LOAD MIN(%Date_Key) AS MinDate,
MAX(%Date_Key) AS MaxDate
RESIDENT Lot_Productie;
LET vmindate = NUM(PEEK('MinDate', 0 , 'minmax_temp'));
LET vmaxdate = NUM(PEEK('MaxDate', 0 , 'minmax_temp'));
LET vToday = NUM(Today());
TempCalender:
LOAD DATE($(vmindate) + ROWNO() -1 ) AS TempDate
AUTOGENERATE $(vmaxdate) - $(vmindate) +1;
//Create the Master Calendar
MasterCalendar:
LOAD
Num(TempDate) AS %Date_Key,
TempDate AS Date,
Week(TempDate) AS Week,
Year(TempDate) AS Year,
Month(TempDate) AS Month,
Day(TempDate) AS Day,
WeekDay(TempDate) AS WeekDay,
'Q' & Ceil(Month(TempDate) / 3) AS Quarter,
Date(MonthStart(TempDate),'MMM-YYYY') AS MonthYear,
Week(TempDate) &'-'& If(Week(TempDate)=1 AND
Month(TempDate)=12,Year(TempDate)+1,
If(Match( Week(TempDate), 52, 53) AND
Month(TempDate)=1, Year(TempDate)-1,
Year(TempDate))) AS WeekYear,
Dual(Year(TempDate) & '-Q' & Ceil(Month(TempDate)/3),
Year(TempDate) & Ceil(Month(TempDate)/3)) AS QuarterYear,
InYearToDate(TempDate, $(vToday), 0) * -1 AS CYTDFlag,
InYearToDate(TempDate, $(vToday), -1) * -1 AS LYTDFlag,
InMonthToDate(TempDate, $(vToday), 0) * -1 AS CMTDFlag,
InMonthToDate(TempDate, $(vToday), -1) * -1 AS LMTDFlag,
InWeekToDate(TempDate, $(vToday), 0) * -1 AS CWTDFlag,
InWeekToDate(TempDate, $(vToday), -1) * -1 AS LWTDFlag
RESIDENT TempCalender
ORDER BY TempDate ASC;
DROP TABLE TempCalender;
DROP TABLE minmax_temp;
