How many Mondays, or any other day, between 2 dates...

Hi,

I have product sales data from a restaurant chain that covers date ranges that are pre, during, and post sales promotions. The pre period defaults to 60 days before the promotion start date and the post period is set to 30 but they may vary depending on the client/supplier goal. The promotion period can be anything from days to months.

One of the statistics they want is average sales / day of the week (Monday, Tuesday, etc.) for each promotion but because nothing is set to full week periods, I cannot just sum the total for Monday sales and divide it by the number of weeks to get the average sales for Mondays. To meet this requirement, I am going to have to count the number of “days" in each period (pre, during and post promotion).

Does anyone know of a way to determine the correct number of days of each day of the week in a defined (but flexible) date range, or a neat way to do the “average sales per (weekday)” without resorting to Grade 1 math.

Regards

tC/.

hi @TigerCole ,
here’s my suggestion

days.knwf (27.1 KB)

2 Likes

I managed to resolve this issue. I don’t think that is the most elegant solution, but it gave me the result I need.

My solution is to create a date range between the dates at either end of the whole date range (1st day of pre-promotion to the last day of post promotion). Use an "Extract Date & Time Fields) node to extract the day number and name and then use a rule engine node to determine which phase of the promotion each day. Finally, a “Pivoting” node to create a table with the count of each day of the week.

I joined the table to the pivot of the sales data and got my average sales per weekday by doing the calculations for pre-, promotion, and post-sales with a “Column Expressions” node which was inside a Group Loop to calculate for each day of the week.

Could you please share your workflow?

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.