
1. Re: Is There A Way To Calculate The Weighted Average Life (WAL) Of Dollars For A Time Series? A Sumproduct?
Jonathan Drummey Jul 25, 2012 2:21 PM (in response to Joseph Pirraglia)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 nonaggregate (which I think of as a rowlevel value). Tableau tries to do as much of the calculation as possible within the underlying data source and mixing aggregates and nonaggregates would get really hairyscary 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?
Joseph Pirraglia Jul 25, 2012 3:27 PM (in response to Jonathan Drummey)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.

WAL Example.xlsx 27.2 KB


3. Re: Is There A Way To Calculate The Weighted Average Life (WAL) Of Dollars For A Time Series? A Sumproduct?
Jonathan Drummey Jul 26, 2012 6:13 AM (in response to Joseph Pirraglia)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

Sumproduct jtd edit.twbx.zip 37.5 KB


4. Re: Is There A Way To Calculate The Weighted Average Life (WAL) Of Dollars For A Time Series? A Sumproduct?
Joseph Pirraglia Jul 26, 2012 7:15 AM (in response to Jonathan Drummey)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?
Jonathan Drummey Jul 26, 2012 9:20 AM (in response to Joseph Pirraglia)The builtin 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:
http://community.tableau.com/thread/118338
And here’s an Idea to have the totals show what is visually displayed in the view: http://community.tableau.com/ideas/1232
Jonathan

6. Re: Is There A Way To Calculate The Weighted Average Life (WAL) Of Dollars For A Time Series? A Sumproduct?
Joseph Pirraglia Jul 27, 2012 6:28 AM (in response to Jonathan Drummey)I am going to play around with this some more in my dataset but this gives me what I need to work with. Thanks again for walking me through this!