6 Replies Latest reply on Sep 22, 2013 4:19 PM by Alex Kerin

# How do I calculate a weighted, multi-part, running average for modeling complex projections?

Is it possible to do something in Tableau similar to what I did in Excel on the attached workbook?  Instead of having E-avg come from Excel data, I would like to calculate it like the following Excel formula, where column B (from Excel) is measure E (in Tableau).

=AVERAGE(AVERAGE(B132:B141)*0.9,AVERAGE(B122:B131)*0.7,AVERAGE(B112:B121)*0.5,AVERAGE(B102:B111)*0.3,AVERAGE(B92:B101)*0.1)

In my attached workbook, I manually added data to the Excel sheet for "E Projection" set at -1.5% Projection Rate so you can see what I am shooting for in the “Projections (E-Excel C-Tableau)” sheet of my Tableau workbook.  I want to calculate E-avg dynamically in “C Projection (Tableau)” so that the Projection Rate parameter will control both measures accordingly.  (I'm sure there also might be a simpler formula for getting the weighted running average I'm looking for, but this formula comes close to calculation I need.)

Thanks,
Jonathan

• ###### 1. Re: How do I calculate a weighted, multi-part, running average for modeling complex projections?

You can do this in Tableau because we can set the window of window_avg:

(0.9*window_avg(attr([E]),-9,0)

+0.7*window_avg(attr([E]),-19,-10)

+0.5*window_avg(attr([E]),-29,-20)

+0.3*window_avg(attr([E]),-39,-30)

+0.1*window_avg(attr([E]),-49,-40))/5

If you have row on the view, you can set the calculation to Table down, but I prefer to explicitly set the sort:

• ###### 2. Re: How do I calculate a weighted, multi-part, running average for modeling complex projections?

Nice.

• ###### 3. Re: How do I calculate a weighted, multi-part, running average for modeling complex projections?

Thanks, Alex!  That will work nicely as long as I keep as many rows in my view that the average will be calculated from.

• ###### 4. Re: Re: How do I calculate a weighted, multi-part, running average for modeling complex projections?

Oops.  I spoke a bit too soon.  The window_avg works just fine for calculating the running average, but I'm still having difficulty figuring out how to use it correctly for the parameter controls that I am intending.  On my updated attached workbook, the sheet "Partial Projections avg100" shows a simplfied use of window_avg but it is not yet tied to the Projection Rate control, so the Projection Rate only affects the "E" measure and not the "C" measure.  The sheet "Full Projections avg100" is my failed attempt at also connecting the "C" measure to the Projection Rate, but you can see from the graph that the C Projection does not follow the same type of curve as in the other sheet.  It is only looking at the current E Projection value rather than the running average from window_avg.

Can you tell me what I am doing wrong?  It seems so close.  Thanks so much for your help!

Jonathan

• ###### 5. Re: Re: Re: How do I calculate a weighted, multi-part, running average for modeling complex projections?

My apologies!  With a little extra time looking at it, I was able to find my mistake.  Fixed version is attached.  Thanks so much for the help in guiding me down the right path!

• ###### 6. Re: How do I calculate a weighted, multi-part, running average for modeling complex projections?

You're welcome.