1 Reply Latest reply on May 10, 2017 12:40 PM by Elliott Stam

    Facing Problem in Calculating Amount for Previous Month

    panda smrutirekha

      I have a ref date field as below. Last 13 months end date based on Today's Date

       

      When no date is selected, it should take max of Ref Date and compute Previous month amount.

      Suppose for 9th may, it should take dates for 01/04/2017 to 30/04/2017 and return the amount.

       

      If I select a date, For EX: 30/04/2017, then it will take Mar 1st to Mar End and compute the amount.

       

      Could someone help me on this??

        • 1. Re: Facing Problem in Calculating Amount for Previous Month
          Elliott Stam

          Hi Panda,

           

          You'll want to create two formulas...

           

          Max Date:

          { FIXED : MAX([Ref Date])}

          *the above formula computes the max "Ref Date"

           

          Your primary calculation would be something like this (assuming your date control is a parameter)...

          New_Calculation:

          IF [Date parameter] = "No Date"

          THEN

            IF

            DATETRUNC('month', [Ref Date]) = DATETRUNC('month', DATEADD('month', -1, [Max Date]))

            THEN [Amount]

            ELSE 0

            END

          ELSE

          IF

          DATETRUNC('month', [Ref Date]) = DATETRUNC('month', date([Date parameter]))

          THEN [Amount]

          ELSE 0

          END

          END

           

           

          Hope that helps,

           

          Elliott Stam - InterWorks