how to calculate debt aging

[#1]

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.

[#2]

Something like file attached

[#3]

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

[#4]

see file attached

Regards

RL

[#5]

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.

[#6]

Yes it is possible. Please see file attached

Regards

R

[#7]

Thanks Lakhina,

This part does not work,

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

[#8]

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

[#9]

still gives me -

[#10]

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

Could be your data structure! Please post example if possible

Regards

R