1 Reply Latest reply on Aug 23, 2016 5:22 PM by Stoyko Kostov

    Last month of last quarter

    lalitha p

      Hi everyone,

       

      I have a requirement like this.

       

      X is calculated field, X = Countd(customers) for the selected Month

       

      Now i want to calculate Y in such a way that my Y should be like this.

       

      Y = countd(customer number) of Last Month Last quarter.

       

      suppose if i select "Aug" then my X and Y should be like this.

      X = Countd(customers) for Aug month

      Y = Countd(customers) for the June Month (Because my previous quarter is Apr-May-June) - So last month last quarter

      Z = X - Y

       

      Can anyone please help me up.

        • 1. Re: Last month of last quarter
          Stoyko Kostov

          Hi Lalitha,

           

          Here's what you can do.

           

          1. Create calculated field DateTruncMonth with definition

          IF(DATETRUNC('month', [Date])=DATETRUNC('month', TODAY())) THEN [Customer] ELSE NULL END

          2. Create calculated field DateTruncQtr with definition

          IF(DATETRUNC('month',[Date])=DATEADD('month', 2, DATEADD('quarter', -1, DATETRUNC('quarter', TODAY())))) THEN [Customer] ELSE NULL END

          3. Create calculated field Diff with definition

          COUNTD([DateTruncMonth])-COUNTD([DateTruncQtr])

           

          This should give you what you need.

           

          I'm attaching a sample workbook.