Rolling averages across groups and Categories
I have set up some equations that are applied on a monthly basis for fiscal years. I am having a couple of problems:
The first problem is that when I establish a value for the first month in the FY for a subsequent calculation (like a running total calculation), the running total formula only pertains to the FY. When I create a new FY, the first month does not take information from the previous FY to continue the calcuation.
The second problem is similar to the first, however this time I am trying to calculate a running average that spans a number of FYs. If I use a 3 month running average, the first three months of every FY are not calculated, as opposed to the first 3 months of all FYs combined.
I have attached the model I am working on. The aim of the model is to forecast forward expected costs and revenues based upon historical data, but I am really simply trying to figure out how to get the model to span categories. It seems that if I establish any initial conditions for a particular category, they get carried forward to subsequent categories.
Any help appreciated.
Doug
Well, out of frustration, I tried a couple of things. I created a new item that simply copied (equaled) the data from my backward looking moving average item formula and then substituted the name of the new item into my forward looking moving average formula. Same #SIZE error occured, so it had nothing to do with one formula referencing another (assuming that is how Quantrix works). I then simply re-typed my forward looking moving average formula and voila!, it worked. I have no idea as to what was wrong with my original formula. What I posted above are direct copies from the formula bar in Quantrix. I can’t see anything wrong with what is written there. Mystery solved, but not sure why!
Doug
Doug/Chris,
Another option for the formula to calculate a 3-month rolling average is to use “selectbetween” (Mike mentions this above).
In the forecast model, that formula would look like this:
Three Month Average = average(selectbetween(Cost of Revenue:Year:Month, Date::Period:Year:Month, Date::Period:Year:Month-2, Date::Period:Year:Month, 1))
It’s a bit simpler than using individual selects to capture each period in the range. It is also easily used for other moving averages by just changing the offset for lookup_value1 (the third argument), which represents the beginning of the range.
Attached is a v2 of the forecast model.
Chris Houle
Thanks, Chris for these equations. I think my error was referring to the historical running average as the data source for calculation of the forecast running average, although I’ not quite sure why this would not work. If I run either of my equations on data, I don’t have the problem. Is there a way in Quantrix to to only look at results of a calucation in a cell as opposed to the underlying formula?
Thanks for your assistance.
Doug
Create a Date matrix with Year, Month, and Date Value categories. Month will have Jan-Dec and YTD. Date Value will have an item called Period. See attachment.
Period = #Month + 12 * (#Year – 1) SKIP YTD
This will calculate an average using the 3 months prior to the current month:
Three Month Average = clearerror(average(select(Cost of Revenue:Year:Month, Date::Year:Month:Period, Date::Period – 3), select(Cost of Revenue:Year:Month, Date::Year:Month:Period, Date::Period – 2), select(Cost of Revenue:Year:Month, Date::Year:Month:Period, Date::Period – 1)), 0) skip YTD
[color=black:3e4d8g0i][font=’Arial’,’sans-serif’]Create a Date matrix with Year, Month, and Date Value categories. Month will have Jan-Dec and YTD. Date Value will have an item called Period. See attachment.
Period = #Month + 12 * (#Year – 1) SKIP YTD
This will calculate an average using the 3 months prior to the current month:
Three Month Average = clearerror(average(select(Cost of Revenue:Year:Month, Date::Year:Month:Period, Date::Period – 3), select(Cost of Revenue:Year:Month, Date::Year:Month:Period, Date::Period – 2), select(Cost of Revenue:Year:Month, Date::Year:Month:Period, Date::Period – 1)), 0) skip YTD[/font][/color:3e4d8g0i]
[color=black:ggcir7f0][font=’Arial’,’sans-serif’]Create a Date matrix with Year, Month, and Date Value categories. Month will have Jan-Dec and YTD. Date Value will have an item called Period. See attachment.
Period = #Month + 12 * (#Year – 1) SKIP YTD
This will calculate an average using the 3 months prior to the current month:
Three Month Average = clearerror(average(select(Cost of Revenue:Year:Month, Date::Year:Month:Period, Date::Period – 3), select(Cost of Revenue:Year:Month, Date::Year:Month:Period, Date::Period – 2), select(Cost of Revenue:Year:Month, Date::Year:Month:Period, Date::Period – 1)), 0) skip YTD[/font][/color:ggcir7f0]
[color=black:ggcir7f0][font=’Arial’,’sans-serif’]Chris[/font][/color:ggcir7f0]
[color=black:d6d88zx3][color=black][font=’Arial’,’sans-serif’]Create a Date matrix with Year, Month, and Date Value categories. Month will have Jan-Dec and YTD. Date Value will have an item called Period. See attachment.
Period = #Month + 12 * (#Year – 1) SKIP YTD
This will calculate an average using the 3 months prior to the current month:
Three Month Average = clearerror(average(select(Cost of Revenue:Year:Month, Date::Year:Month:Period, Date::Period – 3), select(Cost of Revenue:Year:Month, Date::Year:Month:Period, Date::Period – 2), select(Cost of Revenue:Year:Month, Date::Year:Month:Period, Date::Period – 1)), 0) skip YTD[/font][/color:d6d88zx3][/color]
Create a Date matrix with Year, Month, and Date Value categories. Month will have Jan-Dec and YTD. Date Value will have an item called Period. See attachment.
Period = #Month + 12 * (#Year – 1) SKIP YTD
This will calculate an average using the 3 months prior to the current month:
Three Month Average = clearerror(average(select(Cost of Revenue:Year:Month, Date::Year:Month:Period, Date::Period – 3), select(Cost of Revenue:Year:Month, Date::Year:Month:Period, Date::Period – 2), select(Cost of Revenue:Year:Month, Date::Year:Month:Period, Date::Period – 1)), 0) skip YTD
Create a Date matrix with Year, Month, and Date Value categories. Month will have Jan-Dec and YTD. Date Value will have an item called Period. See attachment.
Period = #Month + 12 * (#Year – 1) SKIP YTD
This will calculate an average using the 3 months prior to the current month:
Three Month Average = clearerror(average(select(Cost of Revenue:Year:Month, Date::Year:Month:Period, Date::Period – 3), select(Cost of Revenue:Year:Month, Date::Year:Month:Period, Date::Period – 2), select(Cost of Revenue:Year:Month, Date::Year:Month:Period, Date::Period – 1)), 0) skip YTD
[quote="Doug D":1lcrme5d]Trying something with these rolling average formulas. I have set one formula to calculate the rolling average history which works out ok. I have set up another formula to produce a rolling average forecast, using the data generated from the rolling average history. When I do this, I get “#SIZE” error, which is an inconsistency between the value list and key list sizes, according to the help file. Any ideas appreciated. The two formulas are below:
the history formula:
Moving RevAvrge Hisotry=average(selectbetween(Revenue:FYE:Month, Key::Period:FYE:Month, #FYE:Month-12,#FYE:Month,1))
the forecast formula:
Moving RevAvge Forecast=average(selectbetween(Moving RevAvrge Hisotry:FYE:Month, Key::Period:Month, #FYE:Month,#FYE:Month+3,1))
Any help appreciated
Doug[/quote:1lcrme5d]
Hi – Can you post the model file?
Trying something with these rolling average formulas. I have set one formula to calculate the rolling average history which works out ok. I have set up another formula to produce a rolling average forecast, using the data generated from the rolling average history. When I do this, I get “#SIZE” error, which is an inconsistency between the value list and key list sizes, according to the help file. Any ideas appreciated. The two formulas are below:
the history formula:
Moving RevAvrge Hisotry=average(selectbetween(Revenue:FYE:Month, Key::Period:FYE:Month, #FYE:Month-12,#FYE:Month,1))
the forecast formula:
Moving RevAvge Forecast=average(selectbetween(Moving RevAvrge Hisotry:FYE:Month, Key::Period:Month, #FYE:Month,#FYE:Month+3,1))
Any help appreciated
Doug
OK figured it out. The forward looking formula looks like:
Moving Cost Average=average(selectbetween(Cost Total:FYE:Month, Key::Period:FYE:Month, #FYE:Month,#FYE:Month+3,1))
You have to reverse the order of the values as indicated below to get it to look behind
Moving Cost Average=average(selectbetween(Cost Total:FYE:Month, Key::Period:FYE:Month, [u:330f2xk0]#FYE:Month-3,#FYE:Month[/u:330f2xk0],1))
Thanks,
Doug
I found another thread with some information that I have added to the model. I entered an equation as follows:
Moving Cost Average=average(selectbetween(Cost Total:FYE:Month, Key::Period:FYE:Month, #FYE:Month,#FYE:Month+12,1))
To make this work, I had to set up a key matrix, which worked out fine. However, this particular formula is forward looking up to 12 months. How do I get it to look behind – to get the running average of what has happened? If I change the formula to
Moving Cost Average=average(selectbetween(Cost Total:FYE:Month, Key::Period:FYE:Month, #FYE:Month,#FYE:[u:2fpr67wx]Month-12[/u:2fpr67wx],1))
I get a Div/0 error for all entries, even if I change 12 to something smaller and increase the number of years.
Thanks
Doug