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

    Last 6 months sales

    Shivangi Mishra

      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

        • Re: Last 6 months sales
          Silambarasan P

          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.

          • 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

              Hi,

               

              Try this..

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