Question

Forecasting month volume by days of the week

  • 9 April 2021
  • 4 replies
  • 526 views

I am trying to forecast my monthly case volume by using month to date actuals, by day, as the predictor.

 

My daily volume fluctuates dramatically - so if I have 5 Fridays in a month vs 4 Fridays my volume can be very different.  I have created a custom dimension to convert Work Date (DD-MM-YY) information into Mon, Tue, Wed, Thu, Fri, Sat, Sun.

Now I am trying to forecast the remaining days of the month volume based on the actuals that have come in by day so far in the month.  How do I create a forecasted day for each day of the week, and then tie it into how many of those days are remaining in the month?

 


This topic has been closed for comments

4 replies

Recently I had developed the same view to achieve what you are trying to do. Below is what I did and it does work quite well. It does require a merged view to achieve what I did.

First you create a query with the date and the day of week dimension. You can only put these two dimensions down on the table. Otherwise the system will not line up every day of the current month. That is very important. I had to use the filter of one month.

You need to use this Table calculation as the only other column

mod(diff_days(to_date("1970-01-05"),${transactions.transaction_date}),7)

https://community.looker.com/topic/show?tid=8737&fid=5

Now you merge this result. The next Query you go back 28 complete days. (or as many weeks as you want to go back) For 28 complete days back you create a calculation that divides it by 4 (number of weeks) 

 

The last Query you get the current results for the current month. This is what you will use to compare what actual (dimension) was made this month vs what is predicted for that days type. 

 

Now merge on date. With the merge you now have an average for each day type that can be used to predict the total for the month with the data of what has already passed and is actual vs what is predicted using day type and number of actual days in the month. So now its some table calculations to get us a final forecasted prediction. 

Table calculations I am using on the merged results:

Change: compares the diff between what happened each day and what the average was. This gives you a percent difference you can use to change the prediction (if you want)

if(diff_days(${transactions.transaction_date},now())<=0,null,(${q2_transaction_items.net_price}-${daily_average})/${q2_transaction_items.net_price})

 Avg (this is set so early in the month the change if to big will be kept to a minimum. +-2% here

if(mean(${change})>0.02,0.02,if(mean(${change})<-0.02,-0.02,mean(${change})))

Projected Revenue: This takes the actual Revenue for each day that passed and used the predicted revenue for each day that has not passed. Merging them together to get you a daily actual and expected plotted out. 

if(diff_days(${transactions.transaction_date},now())=0,(${daily_average}*coalesce(${avg},0)+${daily_average}),coalesce(${q2_transaction_items.net_price},(${daily_average}*coalesce(${avg},0)+${daily_average})))

Sum: gives you a single monthly number to show in single value format.

sum(${projected_revenue})

The final result is a forcasted number that takes into account the type of day in the month and how many of each type of days are in each month and puts it all together to get you a final number. In the Avg code if you dont want to use the past difference between average and actual to change the amount in the future you can simply remove that. I wanted to try and use the past to influence the future a bit. That is what that does.

This is then represented with a single value format with a comparison to show the trending:

 

Thanks will try it and get back to you!

Thanks, I get to the part where I add the second query.  Primary query is simple, I just added in the two columns.  However, I get hung up on the 2nd query that I am merging.  I do not have Day of of the week as a Dimension to add. I can create a table calculation to get a day of the week - but then I have to have Work Date included in my 2nd Query.  So it looks like this:

I end up getting the last 28 days of data, instead of just the 7 rows, one for each day.  If I remove the work date - then the formula to calculate Diff Days doesn’t work.  

Am I able to do this if I don’t already have a specific dimension for day of week?  

So I had the dimension of Day of Week already as you can see in the screen shots. That made getting the average as simple as a division by 4. If at all possible asking to have this dimension added would be the best. That bein said below might just work with table calculations only. 

Thinking about your problem I am wondering if you can do the average on the query that you have using a very round about way in table calculations. I actually had a use for this and built it for something else. But it works!

The code below can be used to create an average for that day type. That will allow you to put the day of week (diff_days in your above example) as the join in the merge. You will merge on the day of week dimension so that it will pull in the correct average for that day of week. This is an ugly hack but it could work. No guarantees. 

Summing rows with a specific condition | Looker Community

 

Field #1: Partition Row Number

if(match(${Diff_Days}, ${Diff_Days})=offset(match(${Diff_Days},${Diff_Days}),-1)
 , 1+row()-match(${Diff_Days},${Diff_Days}) , 1)

 

Field #2: Average (Only appears on last instance of criteria)

if(
 NOT(${Diff_Days} = offset(${Diff_Days},1)),
 mean(offset_list(${performance_total_cases}, -(${partition_row_number}-1), ${partition_row_number})),

null)

 

Field #3: Average Row (For referencing in Field #4)

if(${average}>0,row(),null)

 

Field #4: Total For Rows Matching Criteria

index(${average},  min(offset_list(${average_row}, 0, 5000)))

 

If the above code works. then you would have two queries joined on the day of week dimension. One gives you the day of week for the date. One joins on that day of week number and gives you the average. Let me know if this makes sense and works.