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

# 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

• ###### Re: Get total value from sum between two dates

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

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.