Line Chart not Including Unused Dates with a Linked Master Calendar

[jashinskyjm]

This is my first post, so please just let me know if I need to edit anything. I've searched the forums and haven't been able to find an answer to this problem.

I believe I have linked a Master Calendar with my actual data with a matching MMM-YYYY 'Date' variable. I've included a picture here to hopefully show what I have setup. The Master Calendar has every instance of my date range from earliest to latest.

However, when I set up a Line Chart with my data filtered to a limited sample, it does not include all dates from my Master Calendar. Instead it only includes the dates for which there are data.

Does anyone by chance know how I can ensure the line chart includes the date instances where no values are present (i.e., every date included in my Master Calendar)? I'm happy to provide more information if it would be helpful. Thank you.

[MayilVahanan]

Hi @jashinskyjm 

Based on ur screenshot, subset ratio is 100%. so in that case, hope all dates available in ur datas, might be, the values are zero. Try like below

[jashinskyjm]

Hello @MayilVahanan ,

Thank you for the quick response!

I checked that setting you mentioned and found that I did have it checked.

[MayilVahanan]

Hi @jashinskyjm 

Can you provide sample data / file

[jashinskyjm]

Thank you again @MayilVahanan.

I've attached sample data and below are parts of my script. I've been trying to chart counts of "drug_overdose" by month-year.

I also noticed something else. I wanted to chart the data by month-year, but if I chart if by day-month-year I can see that it is properly including all the dates because all dates. The spikes on the first of the month is because all of the date values were assigned for the first of their month.

However, if I use my Filter Panes to narrow down the results, it stops including all of the dates and only includes those that have values. Could there be a setting in my Filter Panes that is causing the problem?

Load Script:

LOAD
[year_incidence],
[month_incidence],
[Race],
[drug_overdose],
date(MakeDate([year_incidence], [month_incidence], 1),'MMM-YYYY') AS Date
FROM [lib://AttachedFiles/EMS Data (11-23-20).csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

Master Calendar Script:

QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);

Temp:
Load
min(Date) as minDate,
max(Date) as maxDate
Resident EMSData;

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
TempDate as Date,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
YeartoDate(TempDate)*-1 as CurYTDFlag,
YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
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;

[jashinskyjm · Accepted solution]

I just wanted to close the loop on this. I ended up being able to find the answer on this page:

Re: QlikSense Dimension: show all values like QlikView?

Turns out when I used selections with the data it also was applying them to the dates in the Master Calendar. So, if I said only chart "females" the Master Calendar wasn't being used because it did not have values for "female." The answer was to apply a set analysis to force Qlik to include the values from the Master Calendar.

Thank you for your help working through this  @MayilVahanan.