Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I haven't had much of a use for a master calendar in the past as I haven't had much for missing dates that we wanted to fill in. I decided to give it a shot and followed exactly what I found in a QlikView Developer booklet I received in training years ago. The problem is, it works, but it doesn't... my missing dates that don't have stats are not showing up. Spent the last day googling and testing things I found in the community to no avail... Could someone help me figure out the element I'm missing? I've attached a scrambled limited version of the report for an example.
Try something like the below example
Fact:
Load * Inline [
DateTest,Value
01/01/2019,10
18/06/2019,20
];
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
min(DateTest) as minDate,
max(DateTest) as maxDate
Resident Fact;
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
Date(TempDate) as Date
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
and replace DateTest with my date field? I saw something to this extent somewhere but couldn't make sense behind the inline part of the code, what dates am I using here, leave as is?
Do I need the QuarterMap table?
In the MasterCalendar table do I name the field according to join with my original data? I assume this is where I am creating my Month, Year, Day of week fields?
Doesn't seem to have worked
and replace DateTest with my date field?
Yes
I saw something to this extent somewhere but couldn't make sense behind the inline part of the code, what dates am I using here, leave as is?
The Inline table is a example here. You need to use your actual table and the table name instead of Fact next to Resident.
Do I need the QuarterMap table?
It's up to you. If you want you can keep it if not take it out.
In the MasterCalendar table do I name the field according to join with my original data?
Yes, Use TempDate as Yourdatefield in the actual table to create a join.
I assume this is where I am creating my Month, Year, Day of week fields?
Yes, You can create the rest of the calendar based on the TempDate field.
Eg:
'WK'& week(TempDate) as Week,
week(TempDate) as WeekNo,
Date(TempDate) as Date,
Year(TempDate) As Year,
Month(TempDate) As Month,
Num(Month(TempDate)) As MonthNum,
If(DayNumberOfYear(TempDate) <= DayNumberOfYear(Today()),1,0) as IsInYTD,
YeartoDate(TempDate)*-1 as CurYTDFlag
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
etc...
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
Alright, so I found a YouTube video where I was able to get the dates to fill in, I can see them in the table, but they wont display in my pivot table. Is there a chart setting or forced join I have to do between the Master Calendar and my data to make all the dates show? I need the days with zeros to actually show in my charts.
Try this
Sum( Sales ) + 0 * Sum( {1} Sales )
The above one is example. Change the expression as per your fields names.
The calendar script I have given did not work?
For each of my expressions or one? I tried on the sum of the Talk Time and I got zeros for every date. I have expressions that are counts and such, how do I deal with those? Just seems like such an odd round about way to get all date to be represented.
Something like