Qlik Community

New to Qlik Sense

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

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
odassier
Creator II
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
sunny_talwar

Try this

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

View solution in original post

sunny_talwar

Try this in a KPI object

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

View solution in original post

7 Replies
sunny_talwar

Try this

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

View solution in original post

sunny_talwar

Updated the line chart as well

odassier
Creator II
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

sunny_talwar

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

odassier
Creator II
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
sunny_talwar

Try this in a KPI object

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

View solution in original post

odassier
Creator II
Creator II
Author

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