Sum of 'Cost' - Rolling 12 months

[#1]

Hi all

How do I calculate the sum of 'COST' for the last 12 months when a 'GROUP' is selected.

In my spreadsheet I have Date in the format 01/04/2013 going back to 2009, GROUP and COST

DATE                        GROUP                     COST

01/05/2009                  London                      2000

.                             

.                                Sheffield                      1000                             

.                             

.                                Manchester                  500

.

01/04/2013                  London                        540                     

[#2 · Accepted solution]

I've done something like this with set analysis.

sum({$<Date={">=$(=num(addmonths(vDateselected,-12)))"}>}[COST]) will get you a sum of Cost for the last 12 months prior to vDateSelected, whatever that might be. However, Date cannot be a Dimension in the object doing the calculation for that to work.

I recommend using text/button objects to replace the date table with a 12 month table (use layout: conditional on the tables you want to disappear/appear).

Hope this helps!

[#3]

Thanks. Its of much help.  Got the logic.