Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
MalcolmCICWF
Creator III
Creator III

Master Calendar Not Filling in all Days

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.

15 Replies
Uday_Pasupuleti
Partner - Creator III
Partner - Creator III

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;

 

MalcolmCICWF
Creator III
Creator III
Author

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?

 

MalcolmCICWF
Creator III
Creator III
Author

Doesn't seem to have worked

Uday_Pasupuleti
Partner - Creator III
Partner - Creator III

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;

MalcolmCICWF
Creator III
Creator III
Author

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.

 

Capture.PNGCapture1.PNG

Uday_Pasupuleti
Partner - Creator III
Partner - Creator III

Try this

Sum( Sales ) + 0 * Sum( {1} Sales )

 

The above one is example. Change the expression as per your fields names.

Uday_Pasupuleti
Partner - Creator III
Partner - Creator III

The calendar script I have given did not work?

MalcolmCICWF
Creator III
Creator III
Author

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.

  • sum(if(DISPO>=2000 and DISPO<3000,1,0))
  • (sum(if(DISPO>=2000,1,0))-sum(if(DISPO>=3000,1,0)))
    /(
    sum(if(DISPO>=2000,1,0))-sum(if(DISPO>=4000,1,0)))
  • count(distinct(ACCT_NBR))

 

Uday_Pasupuleti
Partner - Creator III
Partner - Creator III

Something like

  • sum(if(DISPO>=2000 and DISPO<3000,1,0)) +0 *  sum({1}if(DISPO>=2000 and DISPO<3000,1,0))
  • (sum(if(DISPO>=2000,1,0))-sum(if(DISPO>=3000,1,0)))
    /(sum(if(DISPO>=2000,1,0))-sum(if(DISPO>=4000,1,0))) +0*
    (sum({1}if(DISPO>=2000,1,0))-sum({1}if(DISPO>=3000,1,0)))
    /(sum({1}if(DISPO>=2000,1,0))-sum({1}if(DISPO>=4000,1,0)))
  • count(distinct(ACCT_NBR))+0*count({1} distinct(ACCT_NBR))