3 Replies Latest reply on Jan 24, 2019 1:11 PM by maneesh.gaddam

    How to calculate last 6 months calculated field

    Anjali Sn

      Hi I'm trying to create a rolling 6 months calculated field (to show the last 6 months beginning from previous month ). I'm doing it as follows:

       

      IF DATEDIFF('month', [Create Date], today()) <=6

       

      AND [Create Date] < today()

       

      then [Create Date]

       

      end

       

       

      But this calculates the last 6 months beginning from this month. i.e., gives the values as July 2018 August 2018 September 2018 October 2018 November 2018 December 2018 January 2018

       

      But I want the values as June 2018 July 2018 August 2018 September 2018 October 2018 November 2018 December 2018

      because currently January month is going on and is not complete.

       

      How do I do that?

        • 1. Re: How to calculate last 6 months calculated field
          maneesh.gaddam

          Anjali,

           

          you could try

           

          [Create Date]>=DATE(DATETRUNC('month',DATEADD('month',-7,TODAY())))

          and

          [Create Date]<=DATE(DATETRUNC('month',TODAY())-1)

          • 2. Re: How to calculate last 6 months calculated field
            Paul Wachtler

            Hi Maneesh,

             

            Your calculation is close but if you subtract a month from the current date, and then datetrunc it, it's only going to take create dates from on or before the first of the previous month.  For example, subtracting one month from January will give you December, then doing a date trunc on that will give you December 1st - so anything created later on in December wouldn't be included

             

            This slight modification will make it work:

             

            [Create Date]>=DATE(DATETRUNC('month',DATEADD('month',-7,TODAY())))

            and

            [Create Date]<=DATE(DATEADD('day', -1, DATETRUNC('month',TODAY())))

             

            Best,

            Paul

            • 3. Re: How to calculate last 6 months calculated field
              maneesh.gaddam

              Hi Paul,

               

              Thanks for the mention. Maybe I'm reading the OP's post incorrectly but if [Created date] is at a day level, then using

              [Create Date]<=DATE(DATEADD('day', -1, DATETRUNC('month',TODAY()))) we aren't gonna pickup anything that is created after 12/01. So that's why I created DATE(DATETRUNC('month',TODAY())-1) which picks end of month (12/31).

               

              As the OP said, he/she wanted to include December 2018, 12/01 wouldn't pick whole of december data, right?