Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

Re: Cumulative Product in Qlik Sense

Try this expression:

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

Capture.PNG

18 Replies
agigliotti
Honored Contributor II

Re: Cumulative Product in Qlik Sense

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

I get 0 as grand total.

Not applicable

Re: Cumulative Product in Qlik Sense

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

Re: Cumulative Product in Qlik Sense

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

Re: Cumulative Product in Qlik Sense

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.

Re: Cumulative Product in Qlik Sense

May be this:

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

Capture.PNG

Not applicable

Re: Cumulative Product in Qlik Sense

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.

Re: Cumulative Product in Qlik Sense

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

Re: Cumulative Product in Qlik Sense

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.

Re: Cumulative Product in Qlik Sense

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

Community Browser