Product of values in Array

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! :slight_smile:

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);

CleanShot 2024-02-11 at 18.02.52@2x

That worked splendidly! Thank you :slight_smile: I haven’t used a JavaScript column yet. I’ll have to dive into that more.

1 Like