8 Replies Latest reply: Feb 19, 2013 2:25 AM by Maxim Lopatkin RSS

    cumulative analysis

      suppose i have two column date and sales

      2006          2300

      2007          2700

      2008          3100

      2009          3500

      2010          4500

      2011          3900


      when i select year 2010, i want show the sales from year 2006 to 2010

        • cumulative analysis
          Stefan Wühl

          So you want to see sales from minimum year to selected year?


          Try this as expression (in a text box or as expression in a chart with dimension year):


          =sum({<year = {">=$(=min({1} year))<=$(=max(year))"} >} sales)

            • cumulative analysis



              I want the solution for pivot table along with two dimension.

              And suppose that when i select a year 2010 , i should see the sales as 16100


              =sum({<year = {">=$(=min({1} year))<=$(=max(year))"} >} sales) , this expression work in text but when i used in chart in does'nt work..


              I want see the Following output


              2007    2300

              2008    5000

              2009    8100




                • cumulative analysis
                  Stefan Wühl

                  Ok, you haven't mentioned a pivot table and a second dimension so far...


                  You can get the cumulative in a pivot table using rangesum() function together with chart inter record functions like above. Note that the appropriate chart inter record function will depend on your chart layout. I assume your year is the dimension to the left and second dimension (maybe Customer) is dragged to the top.


                  Then you can use something like:

                  =rangesum(above(sum({<year = {">=$(=min({1} year))<=$(=max(year))"} >} sales),0,RowNo()))


                  Hope this helps,


                    • cumulative analysis

                      Above expression was correct for pivot table..

                      If possible can i see the folowing output:-


                      after Apply the formula my pivot table look like this


                      2007     20

                      2008     50

                      2009     90

                      2010     140

                      2011     200

                      2012     270


                      Suppose now i select year 2009, the output should be like this

                      2009  90

                        • Re: cumulative analysis
                          Stefan Wühl

                          You can try just adding an aggr() advanced aggregation to your expression:



                          rangesum(above(sum({<year = {">=$(=min({1} year))<=$(=max(year))"} >} sales),0,RowNo()))

                          , customer, year)


                          where customer is your second dimension and year your dimension you want to filter.


                          To make this work, the load order of year must be chronological  (because the aggr() function sorts its dimension in load order, not in the order you set in table properties).


                          See also attached.


                          Hope this helps,


                  • cumulative analysis

                    You could also plot a chart with 'year' as the dimension and sales as the expression.

                    Then in the expressions tab of the properties of the chart, select Full Accumulation and Text as Pop-up

                    You can then see the cumulative sales over all years at once!



                    • Re: cumulative analysis

                      You could create new table where each date from your sales corresponds to that date and all dates after that date. F.e.:


                      Date          DateCum

                      2006          2006

                      2006          2007

                      2006          2008

                      2007          2007

                      2007          2008

                      2008          2008


                      Then try to use DateCum as a dimension in Bar Chart. I could share script generating such a table if you need it.