Last 6 months sales

[#1]

Hi All,

I need to calculate the sum of "Sales" for the last six months. I have used "Order Date" date field.

I have used the below expression.

Sum({<Year={"$(=Max(Year))"},Month={">=$(=Month(AddMonths(Max([Order Date]),-5)))<=$(=Month(Max([Order Date])))"}>}Sales)

By using this expression I am not able to get the correct output.

Thanks in advance

Thanks,

Shivangi

[arasantorule]

Hi,

Can try below.

sum({$<CalendarPeriodnum={'>= $(=num(MonthStart(AddMonths(max(CalendarPeriod),-6)))) <= $(=num(MonthEnd(max(CalendarPeriod))))'}

,CalendarMonth=,CalendarQuarter=>}Sales)

Max(Year) will restrict no of months (If 2016 selected for Jan it wont allow last five months of 2015).

CalendarPeriod=MonthName([Order Date])

CalendarPeriodnum=num(MonthName(]Order Date]))  in calendar

Hence suggesting CalenderPeriod.

Thanks.

[MK_QSL]

Create one more field in script..

Date(MonthStart([Order Date]),'MMM YYYY') as OrderMonthYear

Now use as below

SUM({<OrderMonthYear = {">=$(=Date(AddMonths(Max(OrderMonthYear ),-5),'MMM YYYY'))<=$(=Max(OrderMonthYear ))"} >}Sales)

[jayaseelan]

Hi,

Try this..

Sum({<OrderDate={">=$(=Date(Addmonths(Max(OrderDate),-5)))<=$(=Date(Max(OrderDate)))"}>}Sales).