6 Replies Latest reply on Jul 27, 2012 6:28 AM by Joseph Pirraglia

# Is There A Way To Calculate The Weighted Average Life (WAL) Of Dollars For A Time Series? A Sumproduct?

Using the attached tableau workbook – is there a way to calculate the weighted average life of the dollars? I would think of this in Excel terms as:

Sumproduct(Period Index * Dollars)/Sum(Dollars)/12

I get the message “cannot mix aggregate and non aggregate arguments to function” when trying to set this up in “Sumproduct”. How do I get past this issue?

• ###### 1. Re: Is There A Way To Calculate The Weighted Average Life (WAL) Of Dollars For A Time Series? A Sumproduct?

The Period Index calc is a table calculation, which by definition is an aggregate calculation (all table calcs are aggregates, all the WINDOW_xxx functions are aggregates of aggregates). The Dollars that you're trying to multipy by Period Index is a non-aggregate (which I think of as a row-level value). Tableau tries to do as much of the calculation as possible within the underlying data source and mixing aggregates and non-aggregates would get really hairy-scary really fast.

Fortunately, you can use MIN(), MAX(), AVG(), or ATTR() as aggregation functions that return a single value. ATTR() is a special function that returns the value of the given data element if there is one and only one value in the data set, otherwise it returns Null (and is marked as an asterisk * when shown on a text table). So, if you wrap the first Dollars in ATTR([Dollars]) then you should be all set with the rest of the calc.

• ###### 2. Re: Is There A Way To Calculate The Weighted Average Life (WAL) Of Dollars For A Time Series? A Sumproduct?

Interesting. But I think I am missing something.

In order to get the WAL I still need to take the dollar value for a specific date and divide that by the sum of the whole column. From what you are saying that may not be possible.

I have attached an example of the calculation I did in Excel using data from the TWBX file.

Sorry if I am missing something obvious.

• ###### 3. Re: Is There A Way To Calculate The Weighted Average Life (WAL) Of Dollars For A Time Series? A Sumproduct?

I was wondering about that second sum. To do that aggregation across all rows, you'll need to use a table calculation such as TOTAL(SUM([Product])), so your final calculation looks like this:

([Period Index]*ATTR([Dollars]))/TOTAL(SUM([Dollars]))/12

I've set it up in the attached.

Jonathan

• ###### 4. Re: Is There A Way To Calculate The Weighted Average Life (WAL) Of Dollars For A Time Series? A Sumproduct?

This is awesome! It is almost there. Just one more question. How do I get it sum all the numbers in the "Sumproduct" column? I get a * when I try to give me a total sum.

• ###### 5. Re: Is There A Way To Calculate The Weighted Average Life (WAL) Of Dollars For A Time Series? A Sumproduct?

The built-in Grand Total functions aren't a sum (or average, etc.) of what is visually displayed, they use separate queries at a higher level of aggregation. In this case, the grand total queries are issued without the Date dimension that is used in the view. Since the Sumproduct calc is mixing different levels of aggregation, when the grand total calc runs it doesn't have the necessary level of detail and spits out multiple values, which are indicated by the *.

To find out what the total is, you need to create a calculation with the formula WINDOW_SUM([Sumproduct]) and that will give the total.

If you want to show an accurate Grand Total for the view, you will need to create a separate worksheet with the proper totals and merge the two worksheets on a dashboard, or you can build your own Grand Total values with some custom table calculations, here's a post that shows how to do this: