Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
use accumulate 12 step back:
eventdate | value | Sum (value) |
---|---|---|
01/01/2011 | 1147.88 | 1147.88 |
01/02/2011 | 47.29 | 1195.16 |
01/03/2011 | 70.18 | 1265.34 |
01/04/2011 | 10.53 | 1275.88 |
01/05/2011 | 4.50 | 1280.38 |
01/06/2011 | 180.94 | 1461.31 |
01/08/2011 | 177.89 | 1639.20 |
01/09/2011 | 50.63 | 1689.83 |
01/10/2011 | 50.54 | 1740.37 |
01/11/2011 | 49.61 | 1789.98 |
01/12/2011 | 73.72 | 1863.70 |
01/01/2012 | 50.33 | 1914.03 |
01/02/2012 | 54.10 | 820.25 |
01/03/2012 | 12.51 | 785.47 |
01/04/2012 | 4.74 | 720.03 |
01/05/2012 | 69.16 | 778.67 |
01/06/2012 | 1.58 | 775.75 |
01/07/2012 | 6.12 | 600.93 |
01/08/2012 | 122.84 | 545.88 |
01/09/2012 | 25.93 | 521.19 |
01/10/2012 | 150.41 | 621.05 |
01/11/2012 | 6.91 | 578.35 |
01/12/2012 | 18.17 | 522.80 |
01/01/2013 | 18.77 | 491.25 |
01/02/2013 | 4.68 | 441.83 |
01/03/2013 | 10.62 | 439.94 |
01/04/2013 | 4.58 | 439.78 |
01/05/2013 | 10.93 | 381.55 |
01/06/2013 | 2.45 | 382.41 |
01/07/2013 | 7.84 | 384.13 |
01/08/2013 | 5.65 | 266.94 |
01/09/2013 | 30.22 | 271.22 |
01/10/2013 | 12.76 | 133.58 |
01/11/2013 | 58.73 | 185.40 |
01/12/2013 | 6.54 | 173.77 |
01/01/2014 | 17.95 | 172.95 |
01/02/2014 | 39.29 | 207.55 |
01/03/2014 | 9.44 | 206.37 |
01/04/2014 | 7.10 | 208.90 |
01/05/2014 | 8.52 | 206.48 |
01/06/2014 | 8.67 | 212.70 |
01/07/2014 | 2.35 | 207.22 |
01/08/2014 | 3.69 | 205.26 |
01/09/2014 | 12.29 | 187.33 |
01/10/2014 | 14.75 | 189.32 |
01/11/2014 | 4.63 | 135.22 |
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.