2 Replies Latest reply on Jun 15, 2018 6:03 AM by André Coelho

    Problem with Dates - Historical sum

    André Coelho

      Hello!

       

      I have a problem with one of my reviews: I would like to analyze historically how much [Customer] has already paid me. I have the [Type] fields that refer to the types of closures that exist; [Payment Date] refers to the day that [Customer] made payment on the [Value] determined.

       

      Problem: For [Type] = "maintenance ", [Customer] pays monthly from [Payment Date]. That is, if on 01/01/2015 the [Value] = 200, it will pay 200 from 01/01/2015 until the next addition record of the [Type] = "maintenance ". And that continues to the current date TODAY(). Since I do not have all the maintenance records on a monthly basis, I only have records of when the value for maintenance was added. I have a problem calculating the total amount that the customer executed for me.

       

      Any suggestion?

       

      Workbook attached.

        • 1. Re: Problem with Dates - Historical sum
          swaroop.gantela

          André,

           

          I'm not sure if I fully understood, but it was sounding like

          you needed the values for the months in between payments.

           

          If that is the case, I was thinking you could try calculating the

          number of months between each payment, and then multiplying

          that by the maintenance.

           

          I may be getting this completely wrong, but for example

          Empresa 1: Oct 2012: 490, Feb 2013:1230, Aug 2013: 1281

          So for 4 months between oct12 and feb13 the value is 4*490

          then for 6 months between feb13 and aug13 the value is 6*1230

            

           

          Months between:

          DATEDIFF('month',LOOKUP(ATTR([Payment Date]),-1),ATTR([Payment Date]))

           

          This month + previous maintenance total:

          SUM( [Value] ) + [Months Since Last] * LOOKUP( SUM( [Value] ), -1 )

           

          The Running Sum would then be:

          RUNNING_SUM([This Month + Previous Maintenance])

           

          To take into account the months between the last payment and today,

          MAX(DATEDIFF('month',

          { FIXED [Tipo],[Customer]:MAX([Payment Date])},

          TODAY() ) )

          * (IF LAST()=0 THEN SUM([Value]) END) //last payment

          + [RunningSum]

           

          Please see the workbook attached in the Forum Thread.

          If this is completely wrong, would be grateful if you could

          post a small worksheet with what your expected result should be.

          • 2. Re: Problem with Dates - Historical sum
            André Coelho

             

            Swaroop.Gantela, you got it right!

            Works fine.

             

            Thank you.