Calculating Cumulative Returns in Qlik Sense

[odassier]

Hello,

I need help finding the appropriate chart and KPI expression for calculating cumulative returns from daily returns.  The RangeSum(above function will not work since daily returns are not additive.  I am attaching both an XL spreadsheet where cumulative returns are calculated from (given) daily returns and a .qvf file with an example.  In this app you can see a chart of the cumulative returns from XL and the corresponding line using Rangesum(above... which leads to some errors after just a few days already (so you can imagine over several years).  

The expression needs to calculate cumulative return from any date range selected in the date filter on the app.  So this needs to be a dynamic expression (which is the advantage of doing this in Qlik instead of XL). If someone can help me with this app or has an app in which this is done to share, that would be great.

Olivier

[sunny_talwar · Accepted solution]

Try this

exp(RangeSum(Above(log(1+[Daily Returns]), 0, RowNo()))) - 1

[sunny_talwar]

Updated the line chart as well

[odassier]

Brilliant! Thank you so much for this.

I tried to modify your expression to add a KPI but could not make it work.  I get either a "-" or "0.0%".  Any ideas on how to edit the expression for the end-of-period KPI?

Olivier

[sunny_talwar]

What number are you hoping to see in the KPI? The average of all the numbers or something else?

[odassier]

Not the average but the end of period cumulative return. So basically the last number in the table or the last point on the chart (i. e. the cumulative return for the period chosen in the date filter).

Olivier

[sunny_talwar · Accepted solution]

Try this in a KPI object

FirstSortedValue(Aggr(exp(RangeSum(Above(log(1+[Daily Returns]), 0, RowNo()))) - 1, Date), -Date)

[odassier]

Perfect. I get it now, thanks always for your help.
Olivier