Skip to Content

How to put in missing dates?

I need to fill in some missing dates. I created a table with the dates but it doesn't seem to work. I need to be able to filter the report by staff member and once I do all the extra dates are eliminated.

This is what it shows when I filter it by date on the table i created.

Date Table: 1/1/2016

Date Table: 1/2/2016

Date Table: 1/3/2016

Date Table: 1/4/2016

Date Table: 1/5/2016

This is what I see when I filter it based on employee who had entries only the database (which is different from the table i created) for the 1st and 3rd

Date Table: 1/2/2016

Date Table: 1/4/2016

Date Table: 1/5/2016

Even though those dates are from the table i created which has all the dates. It still excludes the dates that the employee didn't have entries on. I need to also show the dates for the days they had no entries.

What am I doing wrong?

Thanks

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Nov 01, 2016 at 02:08 PM

    Have a look at this blog for inserting missing dates between a date range:

    Displaying All Missing Dates In a Range – A Custom Function Sample

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Nov 03, 2016 at 01:02 AM

    In the query editor, create an additional query and just include the date dimension only in the reported objects window. Go back to the report editor and merge both date dimensions (i.e. date(query 1) and date(query 2). Then pull in the merged dimension into your report table.

    Hope this helps!

    Wardie

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 03, 2016 at 07:13 AM

    Hi Kurt,

    Modify the selection formula to explicitly handle NULL dates:

    (isNull({EmployeeTable.Date}) OR {EmployeeTable.Date} = date(0,0,0)) OR

    {Employeetable.Employee} = 'xxx'

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded