# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Qlik Cloud Maintenance is scheduled between March 27-30. Visit Qlik Cloud Status page for more details.
cancel
Showing results for
Did you mean:
Creator II

## Calculating Cumulative Returns in Qlik Sense

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

2 Solutions

Accepted Solutions
MVP

Try this

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

Try this in a KPI object

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

Try this

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

Updated the line chart as well

Creator II
Author

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

MVP

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

Creator II
Author
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
MVP

Try this in a KPI object

`FirstSortedValue(Aggr(exp(RangeSum(Above(log(1+[Daily Returns]), 0, RowNo()))) - 1, Date), -Date)`
Creator II
Author
Perfect. I get it now, thanks always for your help.
Olivier
Tags
Community Browser