Solved: Accumulating the product of values - Qlik Community

Report Inappropriate Content · ‎2011-04-18

I apologize in advance if this question has been answered, but I couldn't find anything in this forum.

Basically, I have a set of market daily returns and want to chart the associated compounded return (i.e. the geometric sum: (1+r1)*(1+r2)*...(1+rn)) for a given date range. The date range would be controlled by a date slider.

Although it seems relatively easy to compute the arithmetic sum for such a filtered date range using the Accumulate feature, I can't find how to compute the geometric sum.

Suggestions anyone?

Thanks -

johnw · ‎2011-04-20

I doubt you'd need the sum(). But I suspect you'll need to account for the very first cell in the column, for which the above() value will be null, and null * a number = null, I believe. I'd probably refer to this by column number, too, unless it has a name.

(1+r)*if(len(above(column(1))),above(column(1)),1)

I doubt it's processing recursively. I strongly suspect it just starts at the top and iterates down.

The exp(sum(log())) solution is the way to do it if you only want the final result, but probably not if you want the result for every step along the way. I assume you can't have a negative market return, so don't need to account for those. But for the curious, here's an expression for that general case. There have been a number of people involved in this expression. I know to attribute parts of it to at least Alexander Schubert and to Haider Al-Seaidy. I made some modifications of my own for performance (converted to set analysis).

if(even(count({<Number={"<0"}>} Number)),1,-1)*exp(sum(log(fabs(Number))))

View solution in original post

Report Inappropriate Content · ‎2011-04-20

I still haven't been able to solve this issue. Does anyone have any thoughts?

rbecher · ‎2011-04-20

Hi,

maybe you can use above( ) function:

=above(sum(1+r)) * sum(1+r)

I think sum( ) is needed as aggregation function.

- Ralf

Astrato.io Head of R&D

ToniKautto · ‎2011-04-20

Is the power calculation what you are looking for? If so please look up pow(x, y)

Report Inappropriate Content · ‎2011-04-20

Ralf - I'll try the solution, if it is possible to recurse, than that should work.

Alternatively, with a little algebra I have come up with the following expression that works: exp(RangeSum(Above(log(1 + return), 0, RowNo()))). However, as the dataset grows, this will get very slow.

johnw · ‎2011-04-20

I doubt you'd need the sum(). But I suspect you'll need to account for the very first cell in the column, for which the above() value will be null, and null * a number = null, I believe. I'd probably refer to this by column number, too, unless it has a name.

(1+r)*if(len(above(column(1))),above(column(1)),1)

I doubt it's processing recursively. I strongly suspect it just starts at the top and iterates down.

The exp(sum(log())) solution is the way to do it if you only want the final result, but probably not if you want the result for every step along the way. I assume you can't have a negative market return, so don't need to account for those. But for the curious, here's an expression for that general case. There have been a number of people involved in this expression. I know to attribute parts of it to at least Alexander Schubert and to Haider Al-Seaidy. I made some modifications of my own for performance (converted to set analysis).

if(even(count({<Number={"<0"}>} Number)),1,-1)*exp(sum(log(fabs(Number))))