8 Replies Latest reply: May 21, 2015 2:51 AM by Tahir Abbas RSS

    Cumulative sum

      Hello,

       

      I have some difficulties for calculating the cumulative sum in an expression to be used in a bar chart. I need the same a result as the "full accumulative" feature in the bar chart cumulative option but in the expression.

       

      I need it in the expression because it must be devided by another field which must not be accumulative.

       

      Here is my scenario. I have the following chart :

       

      cumulative revenue.png

       

      It is simply obtained with a Sum(Revenue) and using the full accumulative feature. The revenue of march 2011 for the blue cohort is the accumulative revenue (jan + feb + march).

       

      The problem is that I need the devide or substract this accumulative revenue by the cohort's Cost of Acquisition wich must not be accumulative.

       

      For example, the CAC of the blue cohort is 2 millions, I should have :

       

      in January 2 millions - January revenue

      in Fev 2 millions - (January + Feb revenue)

      In March 2 millions - (January + Feb + March revenue)

      And so on

       

      Just one part of the expression (the revenue) must be accumulative.

       

      Any idea how to obtain that ?

        • Re: Cumulative sum
          Deepak Vadithala

          Hi,

           

          It will great if you can post your sample QVW. However, you should be looking for RangeSum() & Above() functions to calculate the accumulative sums.

           

          I hope this helps!

           

          Cheers,

          DV

           

          www.QlikShare.com

            • Re: Cumulative sum
              Stefan Wühl

              You are using multiple dimensions ( I  assume they are named liked MONTH and COHORTE), so you might need to additionally use advanced aggregation to get your column segments for the accumulation using above() right, maybe along these lines:

               

              =aggr( rangesum(above( sum(REVENUE), 0, rowno() )), COHORTE, MONTH)  

               

              edit: One more thing, if you are using the advanced aggregation (aggr() function), you'll need to ensure, that MONTH field values load order is chronological ascending, e.g. by loading a master calendar first. The aggr() function's dimension values will always be ordered in load order (not regarding any order you may define for the chart).

               

               

              • Re: Cumulative sum

                Hi,

                 

                I'm trying with RangeSum(Above(Sum(REVENUE),0,RowNo())). The problem is that this fonction depend on the sort order, we can figure it in these two tables :

                 

                cumSum1.png

                 

                cumSum2.png

                 

                The first one is obtained by changing the sort order of the columns and the result is OK.

                 

                The second is the original table obtained from the chart. The problem is that I cannot change the sort order of columns in the chart. So I need an expression where the rowno is obtained from the following sort order : COHORT, MONTH

                • Re: Cumulative sum
                  Ajit Pahuja

                  Hi Deepak,

                  This is with regard to the Custom OnClearAll document even in Qlikview athttp://qlikshare.com/856.

                   

                  I have tried the similar function in my file but I am not able to implement that. The function is exactly written the same way as you have been. It works fine till the concat level but when I go for the the $ expansion, it cripples.

                   

                  Could you provide me with some reasoning for this. Attached is  qvw file explaining the situation in the sky blue text box. The Schema is pretty simple with just two fields over there.

                   

                   

                  The function I am using is

                   

                   

                  =$(=concat(DISTINCT ‘GetSelectedCount(‘& $Field & ‘)’, ‘ + ‘))

                   

                   

                  Thanks.

                  Regards,

                  Ajit

                    • Re: Cumulative sum
                      Deepak Vadithala

                      Hi Ajit,

                       

                      Sorry for the delayed response. You need to use the square brackets.

                       

                      =$(=concat(DISTINCT ‘GetSelectedCount([‘& $Field & ‘])’, ‘ + ‘))


                      I hope this helps! Also, if you are using "Always one value selected" then you need to tweak the logic in the expression. It works great for me.

                       

                      Cheers,

                      DV

                       

                      www.QlikShare.com



                  • Re: Cumulative sum
                    jagan mohan rao appala

                    Hi,

                     

                    PFA file, hope it helps you.

                     

                    Regards,

                    Jagan.