4 Replies Latest reply: Oct 24, 2012 9:42 AM by Carter James RSS

    need to simplify my expression creating: sum accumalted sales per year using interRecords function

      Hi Guys,

       

      If created an expression that gives me the expected accumateled sales starting 2011. (see screenshot). What this expression does is: sum(Sales) of  a year + the sum(Sales) over the previous years.

       

      accumulated sales.JPG

       

      example:

      11.684 + 16.442 = 28.126 (2012) expected accumalted sales in 2012

      28.126 + 24.340 = 52.466 (2013) expected  accumalted sales in 2013

       

       

       

      I 'Solved' this using the following expression:

       

      if(Year=2011,

      sum(Sales) ,

          if(Year=2012,

          sum(Sales)  + before((sum(Sales) ),1),

          if(Year=2013,

          sum(Sales) + before((sum(Sales) ),1) + before((sum(Sales) ),2),

          if(Year=2014,

          sum(Sales)  + before((sum(Sales) ),1) + before((sum(Sales) ),2) + before((sum(Sales) ),3),

          if(Year=2015,

          sum(Sales)  + before((sum(Sales) ),1) + before((sum(Sales) ),2) + before((sum(Sales) ),3)  + before((sum(Sales) ),4),

          if(Year=2016,

          sum(Sales)  + before((sum(Sales) ),1) + before((sum(Sales) ),2) + before((sum(Sales) ),3)  + before((sum(Sales) ),4)

          +     before((sum(Sales) ),5)

                           ))))))   

       

      As you can see there's just to many if statments, which makes it to difficult to manage if another year is added.

       

      My 2 questions are:

      •      How can I simplify my expression? Can I use before once where it looks that the sum(Sales) of all the previous years and adds it with the sales of the year of the colum?
      • Can I create one table like the screenshot above?

       

      I've attached a qlikview sample.

       

      Thanks in advance!

       

      Carter