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

    Problem with Dates - Historical sum

    André Coelho



      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



          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,


          { 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.