6 Replies Latest reply on Mar 7, 2012 11:01 AM by Vusi Nkomo

Moving Average and Forecast

Hi,

I have two measures (Forecast and Actual) which i am plotting over time. I have added a moving average based on Actual, but would like the moving average to "remain" constant or change to zero where "date" is in the future and continue to show what the forecast is. Is this possible in tableau? Have played around without any joy.

• 1. Re: Moving Average and Forecast

Create a calculated field for the Actual field that is similar to the following:

if [Order Date]>today() then 0 else [Actual] end

Use this on your view in place of Actual. Hope this helps! If not, maybe a packaged workbook (twbx file) can be posted?

-Tracy

• 2. Re: Moving Average and Forecast

Hi Tracey,

What I need is for the column with the Moving average to zero off. With the solution you suggested, that i had looked at, the moving average will continue to compute for all periods where I have a forecast as both the forecat and moving average need to display on a single sheet. In the image, once my demand moves to zero (March 2012), I would like my 'Demand: Moving Average' to default to zero, or to remain constant. Either would be acceptable.

• 3. Re: Moving Average and Forecast

You can edit the formula for the moving average the same way, using IF or IIF, based on the date (as shown) or on the "Actual" value for the month being equal to zero or greater than zero.

Bob Laverty

• 4. Re: Moving Average and Forecast

Tried that already and it does not give the desired result as moving average uses Window_AVG and one cannot "ignore" half the window

• 5. Re: Moving Average and Forecast

I mean something like the following:

IIF(SUM([Actual])>0,(WINDOW_AVG(SUM([Actual]),-2,0)),0)

I haven't tested its behavior, but the syntax works.  You should be able to use IFNULL too.  Or test the date instead.

Bob

• 6. Re: Moving Average and Forecast

Thanks a lot. That is what i was looking for.