Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all!
So I have created a Master Calendar in order to be able to show all months in a time series. However, with selections that contain no data observations these months are not showing.
I have already scanned this forum and can see that other people have had the same problem but I haven't been able to incorporate any of the suggested solutions/workarounds.
This is my master calender code:
StartAndEndDates:
LOAD
MIN(MonthVar) AS FirstDate,
MAX(MonthVar) AS LastDate
RESIDENT UC;
// Create variables for min and max
LET vFirstDate = NUM(PEEK('FirstDate', 0, 'StartAndEndDates'));
LET vLastDate = NUM(PEEK('LastDate', 0, 'StartAndEndDates'));
TempCal:
LOAD
Date(MonthStart($(vFirstDate),IterNo()-1),'YYYYMM') as MonthVar
AutoGenerate 1
While MonthStart($(vFirstDate),IterNo()-1) <= $(vLastDate);
MasterCalendar:
LOAD
MonthVar,
YEAR(MonthVar) AS YearVar,
Date(Makedate(Left(MonthVar,4), Mid(MonthVar, 5, 2)), 'YYYY-MM') as Period,
Month(MonthVar) as MonthName
RESIDENT TempCal;
DROP TABLES StartAndEndDates, TempCal;
The linked variable to my data table is the MonthVar variable.
Do I need to modify my set expressions? The simplest of my set expressions is this simply counting distinct IDs where the ticket came in a specific month.
count(
{<filterIncomingMonth={1}>}
distinct ticketID)
Questions
Why even bother with a master calendar if it doesn't work as it is supposed to?
Why doesn't it work?
If this is a known issue, is there somewhere described how to work around this?
Can anybody help me to fix this?
Mostly grateful for help!
Based on what you have shared, everything works as expected. If a month does not have any data of any kind (no nulls, no zeroes, nothing) it will not display on a chart, by default.
You can create "missing" values (https://community.qlik.com/t5/QlikView-Documents/Generating-Missing-Data-In-QlikView/ta-p/1491394) while in the script to make sure that all relevant dimensions (Month in your case) will have at least 1 value with zero.
Additionally, you may want to check the presentation layer as well, depending on the chart, by un-selecting "suppress zero values" in the chart properties > Presentation, un-check "suppress when value is null" in the Dimensions tab, or scaling the chart to show a step of 1 month on the Axes tab.
For one (different objects will have different properties) see the Help site: https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/Bar_Chart.htm
Based on what you have shared, everything works as expected. If a month does not have any data of any kind (no nulls, no zeroes, nothing) it will not display on a chart, by default.
You can create "missing" values (https://community.qlik.com/t5/QlikView-Documents/Generating-Missing-Data-In-QlikView/ta-p/1491394) while in the script to make sure that all relevant dimensions (Month in your case) will have at least 1 value with zero.
Additionally, you may want to check the presentation layer as well, depending on the chart, by un-selecting "suppress zero values" in the chart properties > Presentation, un-check "suppress when value is null" in the Dimensions tab, or scaling the chart to show a step of 1 month on the Axes tab.
For one (different objects will have different properties) see the Help site: https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/Bar_Chart.htm