Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
A short question. For my project in Qlik Sense I have compute the cumulative product of a certain time series. As far as I know the way to calculate the cumulative sum is done by rangesum( above( sum([Expression1]),0,rowno())). Perhaps a variation on this expression can be made to compute a cumulative product?
Thanks in advance for your time.
Sebastian
Correct answer provided by Sunny T (somewhat easier expression):
Exp(RangeSum(Above(Log(([Expression1]), 0, RowNo())))
Try this expression:
Exp(RangeSum(Above(Log((100+Stock1)/100), 0, RowNo())))/Exp(RangeSum(Above(Log((100+Brazil)/100), 0, RowNo())))-1
I have a problem with the total of cumulative expression using rangesum with above.
I get 0 as grand total.
I'm still struggling with the same issue. Could anyone please help out?
Do you have more than one dimensions and you want to do accumulate across all dimensions? May be try this in that case
RangeSum(Above(TOTAL Sum([Expression1]), 0, RowNo(TOTAL)))
If not what do you have and what do you need to get?
Hi Sunny T,
Thank you for your responds. I have implemented your suggestion, but unfortunately it doesn't influence the graph.
I have a large data set. I am using two different files.
The first one is structured as follows:
Dates Stock1 Stock 2 ... Stock T
14-05-2014 -0.04 -0.09 -3.29
15-05-2014 0.09 -0.23 ... 2.35
16-05-2014 0.07 0.01 ... 4.43
... ... .... ... ....
29-09-2016 -0.31 0.22 ... -3.25
The second one is structured as follows:
Dates Brazil Chile ... Turkey
14-05-2014 -1.21 -0.88 -2.12
15-05-2014 0.60 1.09 ... -0.18
16-05-2014 0.42 0.88 ... -0.63
... ... .... ... ...
29-09-2016 -1.18 -0.55 ... -0.11
Where the entries below each stock/country represent the daily return of the corresponding stock/country. Now I would like to calculate the relative cumulative product of each column. That is, e.g. for Stock 1 (which is from Brazil):
((100-0.04)/(100-1.21)-1)*100% = 1.18% (1st term)
( [(100+0.09)*(100-0.04)/100] / [(100+0.60)*(100-1.21)/100] - 1 ) * 100% = 0.67% [Cumulative product]
( [(100+0.07)*(100+0.09)/100]/ [(100+0.42)*(100+0.60)/100] - 1) * 100% = -0.85% [Cumulative product]
etc ...
---------------------------------------------------------------------------------------------------------------------------------------------
In my first attempt I calculated the cumulative sum, but it appeared that was not the object. So what I originally (but wrong) did was:
((100-0.04)/(100-1.21)-1)*100% = 1.18% (1st term)
((100+0.09)/(100+0.60)-1)*100% + 1.18% = 0.67% [Cumulative sum]
((100+0.07)/(100+0.42)-1)*100% + 0.67% = 0.32% [Cumulative sum]
etc...
For the Cumulative Sum I used the following line:
=100*(rangesum(above(sum((100+[$(=Sedol)])/(100+[$(="Mutation Country")])-1),0,rowno())))
----------------------------------------------------------------------------------------------------------------------------------------------
I hope this makes my problem clear. Thank you very much for your time.
May be this:
=(((100+Stock1)*(100+Alt(Above(Stock1), 0))/100)/((100+Brazil)*(100+Alt(Above(Brazil), 0))/100) - 1)
Hi Sunny,
This is indeed the correct answer to what I posted. However, I must apologize for a small inconvenience. I noticed that I misstated the way how I calculated the Cumulative Product. The way I should calculate it is:
-------------------------------------------------------------------------------------------------------------------------------------------
... e.g. for Stock 1 (which is from Brazil):
((100-0.04)/(100-1.21)-1)*100% = ([99.96] / [98.79] - 1 ) * 100% = 1.18% (1st term)
( [(100+0.09)*(99.96)/100] / [(100+0.60)*(98.79)/100] - 1 ) * 100% = ([100.05] / [99.38] - 1 ) * 100% = 0.67% [Cumulative product]
( [(100+0.07)*(100.05)/100]/ [(100+0.42)*(99.38)/100] - 1) * 100% = ([100.12] / [99.80] - 1 ) * 100% = 0.32% [Cumulative product]
etc ...
-------------------------------------------------------------------------------------------------------------------------------------------
As you can hopefully see (this time), we want to make use of numbers calculated in the previous equation.
The answers will only gradually change from the cumulative sum as described in my previous reaction. You can see on the short term, answers are approximately similar to each other (cumulative sum & cumulative product). It does, however, has a significant influence on the longer term.
I'll be more than happy to rewrite anything that is unclear to you. Excuse me once again, I hope that you are able to find some spare time to answer my question. Thank you very much.
Try this expression:
Exp(RangeSum(Above(Log((100+Stock1)/100), 0, RowNo())))/Exp(RangeSum(Above(Log((100+Brazil)/100), 0, RowNo())))-1
Hello Sunny,
Thank you for your reply. I have implemented your suggestion and the graph is looking better than before. Unfortunately, on the long run (2 years) the data still starts to deviate for about 2 percent. I can imagine that this deviation will increase even more in the future.
The exact numbers in my example above are:
a) 1.843303978
b) 0.671368087
c) 0.320491978
I guess our answers start to deviate from the third answer on. I think this is because we are not really taking a cumulative product with your suggestion. I really appreciate all your help so far! I hope you could help me out. Thank you for your time.
I don't see the numbers not matching up
It's a perfect match you can ever ask for
Also, the expression is the exact replica of your proposed logic, if anything will help you reach this output is this expression
Best,
Sunny