Master Calendar and filtered

[EugenioAccornero]

Hi to everybody

I'm working with Qlik Saas edition and I've some doubt on how create a trend graph with Master Calendar. 

1) I've a fact table where each row is dedicated to a specific activity with all its related information; one of the information available is the "Concluded Date" of the activity. Since I'd like to work with this information I've created a Master Calendar in order to have all the dates in the working period available;

2) This is the standard code of Master Calendar I'm using:

MinMaxTemp:
LOAD
Min(FieldValue('Concluded Date', RecNo())) as MinDate, 
Max(FieldValue('Concluded Date', RecNo())) as MaxDate
AUTOGENERATE FieldValueCount('Concluded Date');

LET vToday = NUM(PEEK('MaxDate',0,'MinMaxTemp'));

MasterCalendar_Temp:
LOAD
Date(MinDate + IterNo() - 1) as [Concluded Date] //Create the OrderDate field
RESIDENT MinMaxTemp
WHILE MinDate + IterNo() - 1 <= MaxDate; 

DROP TABLE MinMaxTemp;

MasterCalendar:
LOAD
[Concluded Date],
Week([Concluded Date]) as Week,
Year([Concluded Date]) as Year,
Month([Concluded Date]) as Month,
Day([Concluded Date]) as Day,
Date(MonthStart([Concluded Date]),'MM-YYYY') as MonthYear,
Date(MonthStart([Concluded Date]),'MMM-YYYY') as MonthLYear,
RESIDENT MasterCalendar_Temp;

drop table MasterCalendar_Temp;

-------------------------------------------------------------------------------

3) Starting from this I'd like to create a line chart with:

• on X axis the months/year (i.e. [MonthYear] from Master Calendar)
• on Y axis a count of event with some selections applied by default; in particular the formula is the following:  Count({<[Category]={'CatA','CatB','CatC'},[Activity]={'Emergency'}>} [WS_N])

Now, my issue is the following; doing like this and, eventually, applying some further selection from the filters, it could happen that on some months the count is equal to 0 (that particular subset of data is not existing for that specific month).

In this case those specific months are not shown anymore (see below) in the graph (for example 05-2020 is missing):

 I've tried to add to the measure: + sum({1} 0); it worked partially; now the graph seems good (0 values in missing months are shown) however i cannot filter anymore by Month/year (if I tried to select only 3 months, i can still see all of them on the x axis, but only the selected ones have the count value).

Is there a way to allow the filter of the X axis and, at the same time, have the 0 values on the dates with missing results?

Thanks!!! 

[edwin]

one quick and dirty way is concatenate 0 measures into your fact table.  use all possible combinations for your dimensions.  

[edwin]

of course if you are using average, then its a different/complicated approach as you may need to be selective in what you concatenate (just the missing dates)

[edwin]

this may help:  Generating Missing Data In QlikView - Qlik Community - 1491394

[EugenioAccornero]

I understand the concept, however this will work also if I'm counting the number of row? I can see the point if I'm summing some values, but with the count I will include also the added rows... or not?

[EugenioAccornero · Accepted solution]

I think I've solved by applying this formula:

Count({<[Category]={'CatA','CatB','CatC'},[Activity]={'Emergency'}>} [WS_N])

+

(sum({1 <MonthYear=$::MonthYear>} 0); 

In this way the Date is included in the filter and the selection is applied to the X axis.

Is it correct?

Thanks!