Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'm creating a series of time series line charts ordered by week start (Monday). I would like to keep each week on the x-axis, regardless of if there is data or not. So, for example, my measure is:
=If(Count(distinct Value1&Value2)>0,Count(distinct Value1&Value2),0)
Which produces:
However, when I filter by a category, the x-axis updates to only show dates with available data:
Is there any way to fix this so that all week start dates are shown even if there is no data for that week when a filter is applied?
Hi @michelle22
Create a master calendar with Start and End dates available. Link your date field with master calendar. You will get all dates/weeks/months.
There are lot of master calendar codes/explanations available on websites/YouTube.
Sharing the link for your reference. There are multiple answers and you can select which ever is good for you.
https://community.qlik.com/t5/QlikView-App-Dev/How-to-Create-Master-Calendar/td-p/103109
Thanks & Regards
Thanks for your response. I did create a master calendar table for this purpose but it may be that I have done something wrong?
My script is below:
Temp:
LOAD
Min([Date]) as Mindate,
Max([Date]) as Maxdate
Resident [MyTable];
LET vmindate= Num(Peek('Mindate',0,'Temp'));
LET vmaxdate= Num(Peek('Maxdate',0,'Temp'));
DROP Table Temp;
Calendar:
LOAD
$(vmindate) + IterNo() -1 as Num,
Date($(vmindate) + IterNo() -1) as Date
AutoGenerate 1
While
($(vmindate) + IterNo() -1)<= $(vmaxdate);
Master_Cal:
LOAD
Floor(Date) as [Date],
Month(Date) as [Month],
Year(Date) as [Year],
WeekStart(Date) as [Week Start]
Resident Calendar;
DROP Table Calendar;
Have you suppressed null values in dimension or in presentation?
Yes, I've tried suppressing null values in the dimension, selecting "include zero values" in the data handling, and changing the missing values in Appearance to "show as gaps", "show as zeroes" and "show as connections".