Hi - Is there any way to do a cumulative sum?
e.g. in QLIK Sense I can do
rangesum(above( sum(BaseTotalPL), 0, rowno() ))
and what this does is give me a cumulative sum for every row.
Why would I want this? Very simply: I need to be able to show cumulative P&L day by day. e.g. $10 on 1/1, $20 on 1/2, so I would show $10 on 1/1 and $30 on 1/2.
In addition I need to show geometric cumulative ROR day by day, which could be expressed as sum of logarithms.
Thanks for contacting us.You can achieve this by adding a calculated field and using the PREVIOUS function.
With a similar approach, I guess you can reach a valid result for the logarithms case.
I hope this can help.
This was the first thing I tried, however it had bizarre results in that it instead added to itself, not previous, so perhaps I am doing something wrong.
I have 2 fields: AsOfDate (the date) and BaseTotalPL (daily P&L).
Rows = AsOfDate
Values = BaseTotalPL (aggregated by SUM)
[Sum of BaseTotalPL] + PREVIOUS([Sum of BaseTotalPL],0)
In the attached image, it is clearly added BaseTotalPL to itself, except for the first value for some reason. Thanks for the help!
I wanted to point out that my first answer has an issue, because you will need to a reference to the field itself in the formula.
Let's say your calculated field name is "Acc". So, the right formula for the "Acc" calculated field would be:
Let me know if you find other issue around this.