I collect partner data on either a bi-weekly or weekly basis, on a specific weekday, starting on specific “first report date” for the overall campaign, and the max Partner media “FlightEnd” date.
I thought about using the create Date&Time Range Node with the Interval as 7d or 14d. The issue is that this interval does not skip over weekends and the end date should not necessarily be the max(FlightEnd) date. The end date should be the next report after the Max(FlightEnd) date so that it includes the data through that last date, and I need a node that will tell me what that day would be.
How do I get the list of all the data collection dates?
KNIME Help Table.xlsx (9.1 KB)
Hi @ccastillo , can you provide an example expected output and also include a bi-weekly example.
You mention a problem with weekends but I don’t understand the relevance of weekends if your collection dates are weekdays and you are simply counting whole weeks, so can you elaborate on that issue?
To me it sounds like you just want to know the next date in sequence (based on chosen collection weekday and whether it is weekly or bi-weekly… ) after the max(flight end) date.
Is flight start date relevant?
Hi @ccastillo , I had a little spare time, so decided to make a few assumptions.
Please see attached workflow. It finds the day of the week (as a number between 1 and 7, with 1=Monday) for first reporting date, and the end date, and uses this to determine whether the final reporting date has already passed in the current week and then calcs what the next reporting date would be. It also takes into account if the reporting period is “Weekly” or “Bi-Weekly”. I wasn’t sure what happens if the end date falls on a reporting date, (whether that becomes reporting date, or if reporting date is the following week) so did two branches - doing it both ways.
I changed your data to provide a few different tests. You’ll need to check for yourself if the logic is sound based on your expected outputs, but hopefully it will help with what you need.
forum - Get next reporting date date after a given date.knwf (159.2 KB)
Great @takbb … If I can add some tips too…
With create Date&time range node, you can set the start and the end date/time and specify the range with units (days, houres, month…)
If you’d like to add using days, just set 1d to add or -1d to subtract a day…
Example using today at the end date, adding 1 day as condition with a variable condition to add “any” terms as needed to complete the sequence.
You can make it with other kinds to solve the problem,.
And a plus… you can add the extract date&tiume node to set a indetifier to week name or week number to identify as weekend and can use rows filter to remove it if the week name is sunday or saturday…
I hope that it helps you…
Yes the skipping weekends part is completely irrelevant. I wrote this while tired sorry.
The real issue is not knowing which would be the last data collection date for each partner. The last data collection date should include data through the Max(FlightEnd).
For this purpose I do not think I need the Flight Start date.
Hi @ccastillo , no problem. I made those assumptions too, so let me know if the workflow I posted above does what you need.
This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.