Moving Average Rule


I’m building sales forecasts for this next year, forecasting daily revenue broken down by day periods (morning, day, night). I’m using the lag node to create several lags that I average together (-7, -14, -21, -28, -364) under a math formula node. The issue I can use help with is some past dates are holidays which means the sales were either $0 (closed for the day) or they are much higher than average with the holiday - it’s a restaurant.

How can I modify the math formula or a rule engine node approach to ignore a 0 amount entry in the averaging and/or compensate for an entry above-average? The other approach I’m considering is to manually edit individuals row entries, but I’d like to run this process routinely to update the forecasting accuracy with new actuals.

If data is needed I’m happy to attach some example, but thought I’d try to explain the concept first.

Thank you!

You can use median instead of mean.

Good idea. Thank you.

How do you set up a rule for this though in a math formula, group by, or rule engine?

Right now I’m using math formula: average(x-7, x-14, x-21, x-28, x-364), so how can I enter in the logic so it will change to median if one of the variables is zero?

Oh…you’re saying just use median for ALL of the forecasting…

Median isn’t the right move here…any other suggestions?

Hi there!

Here are my comments:

you can not have this logic in Math Formula node. At least to my knowledge.

You can do it in a Rule engine node simply by adding rules. Before that you should calculate some average amount which you will use to replace 0 amount or above average amount.

If you only calculate forecast like this I think your model could be improved. Also did you calculate some distribution for morning, day, night amounts which you will apply to your forecast?

If you share some data I think there will be a couple of data enthusiast that will take a look and provide advice :wink:


I figured out the logic within the math formula using if and average rules.

I know the forecasting/modeling can be improved beyond this simple averaging approach and I have distributed the data by time of day. Starting simple to get something in production then will consider posting sample data for suggestions on best ways to improve the process with modeling. Thank you ipazin for your input!

1 Like

How did you solve it in math formula? I have seen it know. There is if inside :slight_smile: