4 Replies Latest reply: Jun 1, 2011 3:23 AM by Miguel Angel Baeyens de Arce RSS

    Need help with accumulation formula

    Steffen Lange

      Hi Qlik-Experts!

       

      I need help with a certain accumulation formula.

      I have to accumulate booking amounts over the years in a pivot table. With some search in the community I found a formula which works when the last dimension in the pivot table is the booking year, but not when other dimensions are placed after the year.

       

      Please find a simplified sample of my problem attached. The formula I am using right now is:

       

       

      = if(rowno(),rangesum(sum(Amount  ) , above(sum(Amount) ,1,rowno())),sum( Amount ) )
      

       

       

       

      And I have problems to adapt it in a way, that it is somehow fixed to the year dimension.

       

      Any help is much appreciated!

        • Re: Need help with accumulation formula

          Hi,

          aggregate the whole expression per Year field:

           

           

           =aggr( if(rowno(),rangesum(sum(Amount  ) , above( sum(Amount) ,1,rowno())),sum( Amount ) ) ,Year) 

           

           

          Hope that helps!

          Stefan

            • Re: Need help with accumulation formula
              Steffen Lange

              Okay, works so far for aggregation over the years no matter which dimension is else in the pivot table.

              Thank you so far!

               

              Now I figured out I need to change  the description of the requirement a bit.

              I need to aggregate over the years but only for the dimension which come after the year.

               

              So if the original data looks like this:

               

              %Key1YearSalesRepAmount
              A2008Mary100
              A2009Mary200
              B2008Joe500
              B2009Joe400

               

              I would need the output like this (accumulated over the year but still a drilldown through the dimensions in the pivot)

               

              Year  SalesRep  Amount

              2008  Mary        100

              2009  Mary        300

              2008  Joe          500

              2009  Joe          900

               

               

              The expression from Steve gives me:

               

              YearSalesRepAmount
              2008Mary600
              2009Mary1200