Master Calendar and filtered

EugenioAccornero — Thu, 06 May 2021 16:33:17 GMT

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

Re: Master Calendar and filtered

edwin — Thu, 06 May 2021 18:38:30 GMT

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

Re: Master Calendar and filtered

edwin — Thu, 06 May 2021 18:40:46 GMT

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)

Re: Master Calendar and filtered

edwin — Thu, 06 May 2021 19:03:39 GMT

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

Re: Master Calendar and filtered

EugenioAccornero — Fri, 07 May 2021 12:39:00 GMT

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?

Re: Master Calendar and filtered

EugenioAccornero — Fri, 07 May 2021 12:48:04 GMT

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!

topic Re: Master Calendar and filtered in App Development

Master Calendar and filtered

Re: Master Calendar and filtered

Re: Master Calendar and filtered

Re: Master Calendar and filtered

Re: Master Calendar and filtered

Re: Master Calendar and filtered