Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Cumulative Product in Qlik Sense

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())))

1 Solution

Accepted Solutions
sunny_talwar

Try this expression:

Exp(RangeSum(Above(Log((100+Stock1)/100), 0, RowNo())))/Exp(RangeSum(Above(Log((100+Brazil)/100), 0, RowNo())))-1

Capture.PNG

View solution in original post

19 Replies
agigliotti
Partner - Champion
Partner - Champion

I have a problem with the total of cumulative expression using rangesum with above.

I get 0 as grand total.

Not applicable
Author

I'm still struggling with the same issue. Could anyone please help out?

sunny_talwar

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?

Not applicable
Author

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.

sunny_talwar

May be this:

=(((100+Stock1)*(100+Alt(Above(Stock1), 0))/100)/((100+Brazil)*(100+Alt(Above(Brazil), 0))/100) - 1)

Capture.PNG

Not applicable
Author

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.

sunny_talwar

Try this expression:

Exp(RangeSum(Above(Log((100+Stock1)/100), 0, RowNo())))/Exp(RangeSum(Above(Log((100+Brazil)/100), 0, RowNo())))-1

Capture.PNG

Not applicable
Author

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.

sunny_talwar

I don't see the numbers not matching up

Capture.PNG

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