11 Replies Latest reply: Aug 8, 2012 5:13 AM by Rahul Lakhina

how to calculate debt aging

Hi

I have data with debt amount per month and would like to calculate number of days for one 's debt but the problem some of the months will have zero amount meaning that the previous month's debt has been paid ,and the next month after the zero amount month has a debt amount i will have to start calculating my aging from that month

See below sample

Client                     Amount                  Date

Marais                      600                         01/01/2011

Marais                      400                         01/02/2011

Marais                      0                             01/03/2011

Marais                     200                          01/04/2011

Marais                     130                          01/05/2011

Marais                      20                           01/06/2011

Marais                      0                             01/07/2011

Marais                      0                             01/08/2011

Marais                     1000                        01/09/2011

SO in this case my current debt will be 1000,therefore aging calculation will be from 01/09/2011

Any record/amount prior to zero values is not taken into account.

Thanks.

• Re: how to calculate debt aging

Something like file attached

• Re: how to calculate debt aging

Hey

not exactly what i am looking for,how do i rather flag all the zero amounts and the ones prior to zero amounts  as 0 and rest to one,see below flag column,

Client                     Amount                  Date                                 Flag

Marais                      600                         01/01/2011                      0

Marais                      400                         01/02/2011                      0

Marais                      0                             01/03/2011                     0

Marais                     200                          01/04/2011                      0

Marais                     130                          01/05/2011                      0

Marais                      20                           01/06/2011                      0

Marais                      0                             01/07/2011                     0

Marais                      0                             01/08/2011                     0

Marais                     1000                        01/09/2011                     1

Marais                      500                         01/10/2011                     1

• Re: how to calculate debt aging

see file attached

Regards

RL

• Re: how to calculate debt aging

Thanks Lakhina

Is there a way of doing this in a straight/pivot table.I have trouble with script editor because i have other tables linked to this table and am getting little bit confused.

• Re: how to calculate debt aging

Yes it is possible. Please see file attached

Regards

R

• Re: how to calculate debt aging

Thanks Lakhina,

This part does not work,

Max(total Aggr((if(Amount=0,Date)),Date)

• Re: how to calculate debt aging

Syntax error, try like:

Max (total Aggr((if(Amount=0,Date)),Date))

You missing the ')' marked in Red.

This should give you the last date that had a 0 for Amount

Regards

R

• Re: how to calculate debt aging

still gives me -

• Re: how to calculate debt aging

Please see file attached, it work for the example we have been working off.