Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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)
Hi,
Try this..
Sum({<OrderDate={">=$(=Date(Addmonths(Max(OrderDate),-5)))<=$(=Date(Max(OrderDate)))"}>}Sales).