Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Sa_Rah
Contributor III
Contributor III

Show count data for all dates within filtered date range, including 0 for dates with no record in source data

Hi

I learnt how to use a Master Calendar which is great, and allowed me to show 0 in my chart for dates on which no activity happened.

When I applied a dimension filter though these 0 data points disappeared again. To get around this I used the trick on  this link  which worked perfectly. The formula I am using is:

=count(ID)+0*count({1}ID)

I then applied a date filter to my chart,  and encountered a problem as I saw that the filter no longer just showed the selected date range, but 0 values for all months from the start to the end of my base data set. This makes sense based on the above formula, but I only want the chart to show the date range selected.

I then tried to modify the formula using operators and adding in a date range modifier , and it's almost worked, but when I filter by e.g. financial year  the first data point is the first month in which there is an entry in the base data set, rather than being a 0 value for the first month of the financial year.

My current formula is:

=count(ID)+0*Count({$+1<DateReported={">=$(=Min(DateReported))<=$(=Max(DateReported))"}>}ID)

Can someone help me modify this so that my chart shows data points with 0 values and shows the full date range selected (and only this range)?

Thank you

Labels (3)
1 Reply
Sa_Rah
Contributor III
Contributor III
Author

Wondering if anyone had any suggestions at all?

I found this video which was great, but I got stuck at the same point with the date ranges before and after the first and last events within the filtered data set:

https://youtu.be/BA-YuzaCuy0

Thanks