Rolling 12 Month Total

[#1]

Dear all,

I'm trying to calculate a rolling 12 month total based on my dimensions Year and Month. I think the logic is as follows:

IF selected month = Dec, return YTD calculation (I already have the YTD formula to use), else selected year -1 and selected month +1.

For example, if I select Year = 2010; Month = May, the result will be the sum of sales >=2009 June and <=2010 May i.e. 12 months.

Can someone please help with the correct set analysis code to use? Alternatively if someone has a "cleverer" solution, please help with that.

Many thanks,

APS

[#2]

Hi

you could create a Variable

vDataStart=AddMonths(vDataEnd(), -12);

SUM({$<DateOrder = {'>=$(vDataStart)<=$(vDataEnd)'}>} Price)

[#3]

SUM({$<inv_head.Customer_create_date = {">=$(=min(addmonths(monthstart(inv_head.create_date),-12))) <$(=min(addmonths(monthstart(inv_head.create_date),-12))) "}>} inv_Line.net_dom_amount * [MA Currency Rate])

[#4]

Thanks for the responses. I've substituted in my field to give:

SUM({$<Cal.Month = {">=$(=MIN(ADDMONTHS(MONTHSTART(Cal.Month),-12))) <$(=MIN(ADDMONTHS(MONTHSTART(Cal.Month),-12))) "}>} CEUs)

Unfortunately the result returns zero. As I have two dimensions: Cal.Month & Cal.Year, surely I need to have Cal.Year somewhere in the expression?

[#5]

In the load script use

LOAD

date(monthstart(Date),'MMM-YYYY') as Date_MonthYear,
etc

Create two Variables:
vDate1, vDate2

Set vDate1 =date(addmonths(Date_MonthYear,-11),'MMM-YYYY')

and vDate2 =Date_MonthYear

Use the set analysis formula

=sum({$<Date_MonthYear={">=$(#vDate1)<=$(#vDate2)"}>} despatched_qty*cost_of_sale)

This will calculate for the twelve months rolling

[#6]

I didn't understand the "date(monthstart(Date),'MMM-YYYY') as Date_MonthYear", because if I put below the LOAD, the assumption is this will be find in the excel file, no?