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

# Problem with Dates - Historical sum

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

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]

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

Swaroop.Gantela, you got it right!

Works fine.

Thank you.