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.