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

    Cumulative Product in Qlik Sense

    Sebastian Heselaars

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

        • Re: Cumulative Product in Qlik Sense
          Andrea Gigliotti

          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
            Sebastian Heselaars

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

              • Re: Cumulative Product in Qlik Sense
                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?

                  • Re: Cumulative Product in Qlik Sense
                    Sebastian Heselaars

                    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
                        Sunny Talwar

                        May be this:

                         

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

                         

                        Capture.PNG

                          • Re: Cumulative Product in Qlik Sense
                            Sebastian Heselaars

                            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
                      Sunny Talwar

                      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
                        Sebastian Heselaars

                        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.