Skip to Content
0

How to put in missing dates?

Oct 31, 2016 at 08:50 PM

48

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Kuldeep Ghosh Nov 01, 2016 at 02:08 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Nov 03, 2016 at 01:02 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Abhilash Kumar
Nov 03, 2016 at 07:13 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded