Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I'm struggling with a cumulative return expression for my dashboard.
I've tried a few variants below:
1) RangeSum(Above(Sum(MS.WW4),0,Rowno()),0,5)
2) RangeSum(Above(Sum({<[Date]={'$(=max(Date))'},{<[Date]={'$(=max(Date)-5)'}>}[MS.WW4],0,Rowno()))
3) RangeSum(Above(Sum(MS.WW4),0,5))
4) Rangesum(Above(sum({<Date={'$(=max(Date))'}>}MS.WW4),0,5)
None seem to give me the right answer (or any answer at all). I am attaching the .qvf app here. I need an expression for both the table and a KPI. To complicate things, I've got some filters so there are if statements in there too (see table).
My goal is to use 5-Day cumulative returns from whatever data is selected by the user. The data is daily since Jan 2015 so I need them to be able to go back in time and see if this indicator was predictive.
Thank you for your help.
Olivier
Use the Date field not the autocalendar.Date
as a measure: aggr(rangesum(Below( Sum({<Date>}MS.WW4),0,5)),(Date,(numeric,desc)))
Result:
Thanks to the aggr part, even if u alter the sorting order of ur dates, Qlik will always know how to calculate the rolling 5 days sum:
And thanks to the {<Date>} part, when u select a date, Qlik will return its rolling sum:
Use the Date field not the autocalendar.Date
as a measure: aggr(rangesum(Below( Sum({<Date>}MS.WW4),0,5)),(Date,(numeric,desc)))
Result:
Thanks to the aggr part, even if u alter the sorting order of ur dates, Qlik will always know how to calculate the rolling 5 days sum:
And thanks to the {<Date>} part, when u select a date, Qlik will return its rolling sum:
Hi Omar,
This is perfect, thank you so much. I must remember this one for the future. I deal with investment risk and returns so I'm always in need of cumulative values.
Cheers,
Olivier