I am working on calculating Time Weighted Returns (TWR) in my app. The calculation starts by taking the % return (Daily) from each day and adding 1 to it. To calculate the TWR, you take that day’s DailyValue and multiply it with each day that proceeds it then less 1. So in a three day series the calculation would look like:
(Daily1 + 1)(Daily2 + 1)(Daily3 + 1)-1
I have a column that already equals Daily + 1. I then created a query of the table on itself for any dates that match or are before the row’s date. Then I did a lookup to pull back all the Daily + 1 values that match. It is returning what I would expect.
The problem I’m trying to solve is how to calculate the product of the values in the resultant array.
Thanks for any help or ideas! 
You should be able to use a Rollup->Sum and target it at the Lookup column. Be sure to target the Lookup column directly, and not via the table.
Thanks for your quick reply @Darren_Murphy. Unfortunately I’m not looking for the sum of the values, I’m needing the product of the values.
If the arrays values were 2, 4, 6 I need my result to be 48. The rollup>sum function will produce 12.
oh, apologies - I misread.
hmm, the first thing that jumps into my head is to take a Joined List of the values via the Query, and feed that into a JavaScript column to do the calculation.
Something like the following:
const array = p1.split(', ').map(Number);
return array.reduce((accumulator, currentValue) => accumulator * currentValue);
That worked splendidly! Thank you I haven’t used a JavaScript column yet. I’ll have to dive into that more.
1 Like
