Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
Lionel_Maximus
Contributor III
Contributor III

Master Calendar not showing all dates

Hello,
I have a sales table which contains date and there are some missing dates on which Sale didn't happen. When I associate or join Master calendar it still doesn't work. Can anyone please suggest how to solve this issue... Regards
8 Replies
gwoodard
Partner
Partner

How did you create your Master Calendar?  Did you take the min/max of the dates and build an entry for every date?

sivakumar1994
Contributor III
Contributor III

kindly share the code , so we can identify the issue
Lionel_Maximus
Contributor III
Contributor III
Author

Hello,

This is my master Calendar

QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
min(DELIVERY_DATE) as minDate,
max(DELIVERY_DATE) 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:
Right Join(FACT)
Load
TempDate AS DELIVERY_DATE,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
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
Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

rick_vansoest
Partner
Partner

Hi,

there is probably a better way. But a quick fix would be to add a 0 to every day on your master calendar.

So this is my measure where days without data are missing: Sum(Sales)

missing dates.PNG

This is my new measure: Sum(Sales) + (Count([Datefield]) * 0)

no missing dates.PNG

 

You could also add the value 0 to the master calendar.

Load

0 as 0.Calandar,

TempDate AS DELIVERY_DATE,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
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
Resident TempCalendar


And change your measures to Sum(Sales) + sum(Calendar.0)

 

Lionel_Maximus
Contributor III
Contributor III
Author

Hello,

Thank you for your reply...

(Sum(LPD))/$(MaxDeliveryDay).........This is what i am calculating.....When i select 1-15 Day $(MaxDeliveryDay) gives me 13 because on 2 days there wasn't any activity but i want 15 there.......How's that possible??

Regards

Lionel_Maximus
Contributor III
Contributor III
Author

Hello,

This is my master Calendar

QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
min(DELIVERY_DATE) as minDate,
max(DELIVERY_DATE) 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:
Right Join(FACT)
Load
TempDate AS DELIVERY_DATE,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
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
Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

 

 

(Sum(LPD))/$(MaxDeliveryDay).........This is what i am calculating.....When i select 1-15 Day $(MaxDeliveryDay) gives me 13 because on 2 days there wasn't any activity but i want 15 there.......How's that possible??

Regards

sivakumar1994
Contributor III
Contributor III

Instead of performing join between master calendar and FACT table , keep master calendar as a seperate table and let Delivery date be a key field to assoicate those 2 tables
Lionel_Maximus
Contributor III
Contributor III
Author

Tried didn't work 😕