Display all dates even if there is no data

Question

I am attempting to display all the dates within a range even if they do not have data for the particular date.

Our employees do work 24/7 365 ( like everyone else) so there are dates within my time range that wont pop up. What I have tried is to add and exception that states even if the date has a null to display that particular date.

Currently I have a crosstab with the row being the numbers of items completed and the columns being the date that the item was completed.

For a visual:

04/01/19     04/02/19      04/03/19
   1             2             3

I would like to continue that till the end of my range (the month) but my data skips if nothing was completed for that date.

What I currently have:

04/01/19   04/04/19   04/05/19
   1           6         8

For the rows I have this in the formula bar for my row.

If [Items Completed]> 0 Then [Items Completed)] Else 0

For the columns I have this in my formula bar

If IsNull([Completed Date]) then 0 Else [Completed Date]

Thinking this would give me a 0 in those columns where nothing was done.

Answer 1

You need to create a variable and use the TimeDim function like this...

All Dates = TimeDim([Completed Date])

Then replace [Completed Date] in your table with the [All Dates] variable. You can name it whatever you want. This will result in blank values for the dates for which you do not have data. If you want to have zeroes display for the dates with no data you can apply a custom format setting "Undefined" to "0".

You can find a more thorough explanation with possible variations here.

Enjoy!

Answer 2

The TimeDim method was the basis for me achieving what I needed - namely a line chart that displayed zero values if no one had completed a training course on a particular date in my range. I work in Learning Management System software so this is often required.

The object in my case is called [Course Completed Date (Success)], so here's my variable (which is a dimension, not a measure):

=TimeDim([Course Completed Date (Success)])

Note in my screenshot how the zeros and now displayed. Previously they were missing.