Displaying Full Week in a table

I am running a weekly report which will result in a table with each day of the week and the count next to it.

However, some days have no values to count but I would like to still have them appear in the table with a 0 as the count. I only want the dates to display for that week. (in order from Sunday-Saturday)

example below:

This is what I am getting:
Value Date Count (Value Date)
14-May-2018 5
15-May-2018 2
17-May-2018 3
18-May-2018 3

Ideal Table:
Value Date Count (Value Date)
13-May-2018 0
14-May-2018 5
15-May-2018 2
16-May-2018 0
17-May-2018 3
18-May-2018 3
19-May-2018 0


Hi @Kylek9 -

Here is a simple toy example. It doesn’t convert strings to Datetime, and it’s not based on dynamically generated dates - but it works for your example. I’m sure there are more sophisticated ways to approach the problem!



DateJoinExample.knwf (11.7 KB)

Thank you for the reply, I would like to make the reference dates table to be dynamic as to not need to input each day of the week for each week I run the report

In that case, you could use the Create Date&Time Range node instead of a Table Creator node. This example makes a table of dates for all days in July 2017.

Thanks Scott, you got me on the right track, I attached the workflow that is now dynamic (at least through 2030) below.

The basic logic is:

  • Create a reference date table with every date in the future (mine is until 2030)
  • extract the week and year from both reference and actual dates
  • aggregator -> concatenate these two newly created columns into a new “Year, week” field For both the reference dates and your actual dates
    -Run an inner join between your data and the reference data by matching this “Year, Week” field
  • Filter out all the extra columns

Now you will have all 7 days of your week of interest and you may run a join with your other data and have all weekdays visible regardless of whether there is data or not.

1 Like