Alpha Video Training
Results 1 to 4 of 4

Thread: Ageing Report

  1. #1
    Member
    Real Name
    Walter
    Join Date
    Jun 2003
    Location
    South Africa
    Posts
    456

    Default Ageing Report

    Hi Al,

    I am having trouble creating a ageing report. I want to show on the statement x amount is current, x amount is 30 days etc.

    Not sure how to approach this one. I have added calculated fields to the report, but obviously not working correctly. I tried to use the iif() statement, but not getting the result I wanted.

    Currently I have a table "stat" with the following:
    Ref, Invoice date, Invoice No & amount due
    In this table there are more than one invoice for a specific client.

    My set for the report is as follows:
    Parent: Client
    Child: "stat"

    What would be the "correct" way to approach this.
    Thanks
    Walter

  2. #2
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,300

    Default

    There are several different ways to do this. It's been discussed lots of times. Try searching for "aging receivables", "aging report", "aging invoices".

    1) In Dr. Wayne's Alpha Five: Xbasic For Everyone (written for A5v4) he illustrates a scripted solution which steps through the invoices table and creates an intermediate table. The intermediate table is used to produce the report.

    2) You can use tablesum() calcs with filters based on the current client id.

    3) You can create calc fields that get evaluated for each record, and then totalled in the group footer. In pseudo code these would look like this:

    if today minus inv_date is less than 30 return inv_amt otherwise zero.

    if today minus inv_date is between 30 and 60, return inv_amt else zero.

    If today minus inv_date is between 60 and 90, return Inv_amt else zero.

    Each of these would be totalled in the group footer.

    -- tom

    if today minus inv_date is more than 90, return Inv_amt else zero.

  3. #3
    Member
    Real Name
    Francis Dizon
    Join Date
    Jan 2006
    Location
    Philippines
    Posts
    404

    Default

    Quote Originally Posted by Walter
    Hi Al,
    I am having trouble creating a ageing report. I want to show on the statement x amount is current, x amount is 30 days etc.

    Not sure how to approach this one. I have added calculated fields to the report, but obviously not working correctly. I tried to use the iif() statement, but not getting the result I wanted.

    Currently I have a table "stat" with the following:
    Ref, Invoice date, Invoice No & amount due
    In this table there are more than one invoice for a specific client.

    My set for the report is as follows:
    Parent: Client
    Child: "stat"

    What would be the "correct" way to approach this.
    Thanks
    Walter
    try this calculated expressions:
    UPTO30 = IF(DATE()-Invoice Date<=30,Amount Due,0)
    FROM31TO60 = IF(DATE()-Invoice DATE>30.AND.DATE()-DATE<=60,Amount Due,0)
    ABOVE60 = IF(DATE()-Invoice DATE>60,Total,0)

    works fine with my report.

    i am filtering the records by Paid = .F. on the detail section of the report.
    Paid is flagged on my invoice form during data entry, if paymode = "cash" then paid = .t. else, paid = .f.
    hope this helps..

    the above expression was copied from alphafour version 6 user guide book.

    good luck
    Francis

  4. #4
    Member
    Real Name
    Walter
    Join Date
    Jun 2003
    Location
    South Africa
    Posts
    456

    Default

    Hello Francis,

    Thanks for the reply.

    I did something similar,completed it this morning. I was searching with the incorrect phrases yesterday.

    I have 2 expressions per x value
    1. curr = if(date() - inv_dte < 30,inv_amt,0)
    2. total(calc->curr,grp->customerid)


    Thanks in any case!

    Have a nice day
    Walter

Similar Threads

  1. Report Date Variables in Report Heading
    By davidab in forum Alpha Five Version 6
    Replies: 4
    Last Post: 12-21-2005, 02:33 PM
  2. Report Date Variables in Report Heading
    By davidab in forum Alpha Five Version 6
    Replies: 0
    Last Post: 12-20-2005, 11:35 AM
  3. ageing report
    By vasant panjabi in forum Alpha Five Version 6
    Replies: 1
    Last Post: 07-02-2005, 11:39 PM
  4. Web report similar to filtered desktop report
    By Robert Rough in forum Web Application Server v6
    Replies: 2
    Last Post: 06-08-2005, 10:14 AM
  5. set report summary report non zero balances
    By vasant panjabi in forum Alpha Five Version 4
    Replies: 2
    Last Post: 11-15-2001, 04:45 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •