3 Replies Latest reply: Jul 4, 2016 6:18 AM by Jayaseelan K RSS

    Last 6 months sales

      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




        • Re: Last 6 months sales
          Silambarasan P



          Can try below.


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



          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.



          • Re: Last 6 months sales
            Manish Kachhia

            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)

            • Re: Last 6 months sales
              Jayaseelan K



              Try this..