2 Replies Latest reply: Jul 5, 2013 9:24 AM by Gysbert Wassenaar RSS

    Get total value from sum between two dates

      Good day everyone,

       

      have been searching for this in many old discussions, but well here is my problem.

       

      I am making a chart where in the rows are the values of some products selected. and each column has a date like this:

      01/2013 - 02/2013 - 03/2013

      value    - value      - value

       

      This values are the total of the month and the prevous eleven ones.

      Like this:

       

      01/2013: 01/2013+12/2012+11/2012+10/2012+09/2012+08/2012+07/2012+06/2012+05/2012+04/2012+03/2012+02/2012

      02/2013: 02/2013+01/2013+12/2012+11/2012+10/2012+09/2012+08/2012+07/2012+06/2012+05/2012+04/2012+03/2012

      03/2013: 03/2013+02/2013+01/2013+12/2012+11/2012+10/2012+09/2012+08/2012+07/2012+06/2012+05/2012+04/2012

       

      Here is an expression i was making by all the discussions i read.

       

      = sum({<Date={">=$(max(prev_Date))<=$(max(Date))"}> } Value)

       

      where

      Date: field of the selected date or last date of the value in the certain case.

      prev_Date: field of the date 11 month earlier

      value: the value wanted from each date sum up

      Date: 03/2013

      prev_Date: 04/2012

       

      Please help.

        • Re: Get total value from sum between two dates
          Yair Sharon

          use accumulate 12 step back:

           

           

          eventdate value Sum (value)
          01/01/20111147.881147.88
          01/02/201147.291195.16
          01/03/201170.181265.34
          01/04/201110.531275.88
          01/05/20114.501280.38
          01/06/2011180.941461.31
          01/08/2011177.891639.20
          01/09/201150.631689.83
          01/10/201150.541740.37
          01/11/201149.611789.98
          01/12/201173.721863.70
          01/01/201250.331914.03
          01/02/201254.10820.25
          01/03/201212.51785.47
          01/04/20124.74720.03
          01/05/201269.16778.67
          01/06/20121.58775.75
          01/07/20126.12600.93
          01/08/2012122.84545.88
          01/09/201225.93521.19
          01/10/2012150.41621.05
          01/11/20126.91578.35
          01/12/201218.17522.80
          01/01/201318.77491.25
          01/02/20134.68441.83
          01/03/201310.62439.94
          01/04/20134.58439.78
          01/05/201310.93381.55
          01/06/20132.45382.41
          01/07/20137.84384.13
          01/08/20135.65266.94
          01/09/201330.22271.22
          01/10/201312.76133.58
          01/11/201358.73185.40
          01/12/20136.54173.77
          01/01/201417.95172.95
          01/02/201439.29207.55
          01/03/20149.44206.37
          01/04/20147.10208.90
          01/05/20148.52206.48
          01/06/20148.67212.70
          01/07/20142.35207.22
          01/08/20143.69205.26
          01/09/201412.29187.33
          01/10/201414.75189.32
          01/11/20144.63135.22
          • Re: Get total value from sum between two dates
            Gysbert Wassenaar

            If you want to use the months as a dimension you can't use set analysis expressions because the sets are calculated per chart and not per dimension value. You can try using an AsOf table. See this document: Calculating rolling n-period totals, averages or other aggregations. It looks like you want a rolling 12-month total. The AsOfTable Examples document has an example of that.