Finding the date when the account balance goes from negative to positive 1

[mmck1964]

I have a report that is grouped by Account_Number. I am trying to return the date for each account when the account balance goes from a credit to a debit amount. It could vary from account to account, or even never.

In the example data below, which is in the detail section for the account, I only want to return the date when -4.68 goes to 1.31, or 05/25/2013. As you can see, Acct_Bal did the same thing on 05/18/2013, but I only want to see the latest one. If I ran the report on 05/20/2013, I would want to see the sate 05/18/2013.

{Bill_Date} {Acct_Bal}
05/28/2013 14.72
05/27/2013 9.89
05/26/2013 5.06
05/25/2013 1.31
05/24/2013 -4.68
05/23/2013 -7.81
05/22/2013 -13.37
05/21/2013 10.22
05/20/2013 6.67
05/19/2013 3.35
05/18/2013 0.48
05/17/2013 -15.55
05/16/2013 -22.28

I hope this is enough data and an explanation to see what I am hoping to do.

Thanks!

 

[pmax9999]

I tackled it this way.

Create 3 formulas:

Formula 1: place in report header

WhilePrintingRecords;
Global DateVar DTE := Date(1900,1,1)

Formula 2: place in details section

WhilePrintingRecords;
Global DateVar DTE;

If      Not(OnLastRecord) and
        DTE = Date(1900,1,1)
Then    If      {Table.Acct_Bal} > 0 and
                Next({Table.Acct_Bal}) < 0
        Then    DTE := Date({Table.Bill_Date})
        Else    DTE := DTE
Else    DTE := DTE;

Formula 3: place in report footer

WhilePrintingRecords;
Global DateVar DTE

The result of the third formula will be the date that the balance went from positive to negative.

You may need to adjust the formula to deal with zero balances, depending on whether these should be treated as positive or negative. It also assumes the report has no grouping.

If there is grouping, the first and third formulas may need to be moved, depending on whether the date returned should be at a group or report level.

Cheers
Pete

 

[mmck1964]

Worked perfectly! Thank you!