topic Re: Master Calendar Not Filling in all Days in QlikView

Master Calendar Not Filling in all Days

MalcolmCICWF — Sat, 16 Nov 2024 03:14:30 GMT

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.

Re: Master Calendar Not Filling in all Days

Uday_Pasupuleti — Wed, 19 Jun 2019 15:52:48 GMT

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;

Re: Master Calendar Not Filling in all Days

MalcolmCICWF — Wed, 19 Jun 2019 19:13:50 GMT

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?

Re: Master Calendar Not Filling in all Days

MalcolmCICWF — Wed, 19 Jun 2019 19:55:44 GMT

Doesn't seem to have worked

Re: Master Calendar Not Filling in all Days

Uday_Pasupuleti — Thu, 20 Jun 2019 08:27:17 GMT

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;

Re: Master Calendar Not Filling in all Days

MalcolmCICWF — Thu, 20 Jun 2019 14:59:38 GMT

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.

Re: Master Calendar Not Filling in all Days

Uday_Pasupuleti — Thu, 20 Jun 2019 15:06:06 GMT

Try this

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

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

Re: Master Calendar Not Filling in all Days

Uday_Pasupuleti — Thu, 20 Jun 2019 15:07:54 GMT

The calendar script I have given did not work?

Re: Master Calendar Not Filling in all Days

MalcolmCICWF — Thu, 20 Jun 2019 15:16:08 GMT

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))

Re: Master Calendar Not Filling in all Days

Uday_Pasupuleti — Thu, 20 Jun 2019 15:22:45 GMT

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))

Re: Master Calendar Not Filling in all Days

MalcolmCICWF — Thu, 20 Jun 2019 15:54:16 GMT

tried in one of the expressions, table just turns to this...

I'm seeing why people choose to not use these Master Calendars as much, never had so many issues with something that seems like it should be working

Re: Master Calendar Not Filling in all Days

Uday_Pasupuleti — Thu, 20 Jun 2019 15:57:55 GMT

Try the last count expression on a straight table with one dimension and see whether it works or not.

It has nothing to do with master calendar. It's because there is no data.

So we are trying to create missing dates and data.

Re: Master Calendar Not Filling in all Days

MalcolmCICWF — Thu, 20 Jun 2019 17:56:51 GMT

No data correct, but the point from what I've seen for all of these Master Calendar articles is to fill in dates that are missing so they appear in charts and tables build in QlikView. They can show as zeros, but at least you see these dates charted

Re: Master Calendar Not Filling in all Days

rwunderlich — Thu, 20 Jun 2019 18:28:56 GMT

Uncheck "Suppress Zero-Values" in the Presentation pane of the object.

-Rob

Re: Master Calendar Not Filling in all Days

MalcolmCICWF — Fri, 21 Jun 2019 13:59:44 GMT

I checked those, they are already unchecked.

Re: Master Calendar Not Filling in all Days

Brett_Bleess — Thu, 18 Jul 2019 21:50:51 GMT

Well, I know you found the YouTube video, but I wanted to give you the Design Blog post on the Master Calendar as well, as I thought it might be of some further help potentially, There are also lots of other posts, so feel free to backup a level on the URL and then search again for what you think you may need, there may be another post that will get you the missing piece there.

https://community.qlik.com/t5/Qlik-Design-Blog/The-Master-Calendar/ba-p/1471527

Regards,
Brett