Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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!!!
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!
one quick and dirty way is concatenate 0 measures into your fact table. use all possible combinations for your dimensions.
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)
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?
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!