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
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!
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.
