How do you calculate the sum of sales for rolling 12 months and rolling months 13-24

[smcmilla]

Hi, Can someone help me with this syntax? Or suggest a better formula?

I need a rolling 12 months' sum of sales. This is my formula using today's date 11/14/2016 - 11/14/2017.

sum{<Date={"$(='>=' & date(today()-365) )"}>} Sales)

I also need the sum of sales for rolling months 13-24. This is my formula for 11/14/2015 - 11/14/2016

sum{<Date={"$(='>=' & date(today()-730) )"}>} Sales) - (sum{<Date={"$(='>=' & date(today()-365) )"}>} Sales)

Nothing seems to be working. I need it to work off today's date.

Any help is greatly appreciated.

[Anonymous]

Try

sum{<Date={'>=$(=date(today()-365))'}>} Sales)

[smcmilla]

Hi Wallo,

That did not return any results.

[Anonymous]

do you have some sample data?

[sunny_talwar]

May be this

Last 12 Months

Sum({<Date={"$(='>=' & Date(AddYears(Today(), -1), 'MM/DD/YYYY') & '<=' & Date(Today(), 'MM/DD/YYYY'))"}>} Sales)

Months 13-24

Sum({<Date={"$(='>=' & Date(AddYears(Today(), -2), 'MM/DD/YYYY') & '<=' & Date(AddMonths(Today(), -1), 'MM/DD/YYYY'))"}>} Sales)

[smcmilla]

Hi everyone,

I wanted to share what I ended up using.

I created a variable called IsLast365Days in the calendar load. Then used it in my object.

Here is the code.

If (PeriodDate >= AddMonths(Today(),-12) and PeriodDate < Today(),1) as IsLast365Days,

Sum({<IsLast365Days = {1}, Year= , YearMonth = , Month = >}Sales)   this will give you the rolling 12 months of sales.