I've been using product calculations (Successfully) at the row level to creating a compounding rate of return like the following:
previous_value(1)*([ROR MTD]+1)
I now have a requirement to do some weighted calculations and I need to create calc that does the compounding rate of return for all of the rows (Column GT of the above calculation accessible to calcs at the row level) The basic calc is something like this:
prior months cumulative contribution to return + current months contribution to return * (1+ Prior Months Compound Rate or Return for all rows ie(Cumulative ROR Column total for prior month) In the attached workbook this calc would be the compounding rate or return for Funds A & B.
I was trying to break this all down and the part I'm stuck on is how to write a calc that has the multiplication of all the column ROR values up to the current month. Initially I thought I could just create a new calc that used the total function monthly ROR and then multiply those values using previous_value like I did for the original ROR compounding calc but that didn't seem to work. If there a way to scope a calculation to ignore the dimensions on the rows shelf so that I can make an ROR calc that is for all rows? I made a simple workbook to illustrate this and I'm trying to write a new [PTD ROR ALL] that I can reference in other calcs so that I can do these special weighted returns. Hope that was enough detail to explain what I am trying to do.
Become a Viz Whiz on the Forums!
Support the Community and master Tableau.