18 Replies Latest reply: Dec 6, 2016 10:32 AM by Sebastian Heselaars

# 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?

Sebastian

Correct answer provided by Sunny T (somewhat easier expression):

Exp(RangeSum(Above(Log(([Expression1]), 0, RowNo())))

• ###### 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.

• ###### 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?

• ###### 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)

• ###### 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

• ###### 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

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

• ###### Re: Cumulative Product in Qlik Sense

Works in Qlik Sense also

• ###### Re: Cumulative Product in Qlik Sense

Sound! It seems more than correct. The difference in visualization could also be very good an issue with the data I am provided with. Please give me a day and I'll come back with you. I have to verify my data set and your suggestion with a colleague who is absent today from work.

I cannot state how much I thank you for your help. I am struggling with this "simple" problem for a while now and hopefully we've found the correct solution.

• ###### Re: Cumulative Product in Qlik Sense

Take your time my friend and trust me this is not a simple problem by any means....

Best,

Sunny

• ###### Re: Cumulative Product in Qlik Sense

Hi Sunny T,

Thank you for your patience. The last few days I have tried to figure out what the 1 to 2 % difference in results caused. I am convinced this is data and not formula related. Results are looking great for all the stocks which have a correct data set.

I would like to thank you very much for your effort. This was an important task for me to fix. Now I can focus on retrieving correct data for the missing numbers. I have the difficult task to list one of your answers as correct. Do you have a preference? I think this should be the correct one. The one where you started with...:

Try this expression:

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

As I haven't seen the solution for a cumulative product come by yet on this forums, I think this could hopefully help many others as well. My final solution is a dynamic one and looks as follows (making it a percentage):

=100*(Exp(RangeSum(Above(Log((100+[\$(=Mapping_Sedol)])/100), 0, RowNo())))/Exp(RangeSum(Above(Log((100+[\$(="Mutation Country")])/100), 0, RowNo())))-1)

With kind regards,

Sebastian Heselaars

• ###### Re: Cumulative Product in Qlik Sense

Although I am confident about this, lets take a second opinion from the expert (since I see he is already on the community right now). swuehl can you take a look at the expression I provided?

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

• ###### Re: Cumulative Product in Qlik Sense

A slightly easier variant to my question which is perhaps more understandable to others is that a cumulative product can be calculated as:

Exp(RangeSum(Above(Log(([Expression1]), 0, RowNo())))

Credits to Sunny T for coming up with the solution.

• ###### Re: Cumulative Product in Qlik Sense

Agreed!!

I also want to thank you for your detailed response after getting your task accomplished. A lot of the times we help others and people don't even come back or don't do a good job of explaining what actually helped them in the end. I feel that if each of us (both poster or respondents) provide detailed information, we are going to make this community a much better place.

Best,

Sunny

• ###### Re: Cumulative Product in Qlik Sense

I agree with you for more than 100%. Qlik Sense is an amazing visualization tool with much potential. Together we can make it an even better product.