Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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