Bryan, Once you calculate total days outstanding (.#DATE-TransDate), you then put the amount of the invoice in one of the age groups. You will have one line for each transaction.
You can include the subtotals as part of your view using Select InvoiceTotal=s, D15=s, D30=s, D45=s, etc. The sum of your invoice amounts should equal the combined total of all the aged subtotals. SET NULL ' ' CREATE TEMPORARY VIEW vwAgingAR (TransID,InvoiceTotal,Days,D15,D30,D45,D60,D60plus) + AS SELECT TransID,InvoiceTotal,(.#DATE-TransDate), + (IFLT( (.#Date-TransDate),16,InvoiceTotal,null)), + (IFLT( (.#Date-TransDate),31,(IFGT( (.#Date-TransDate),15,InvoiceTotal, NULL)), NULL)), + (IFLT( (.#Date-TransDate),46,(IFGT( (.#Date-TransDate),30,InvoiceTotal, NULL)), NULL)), + (IFLT( (.#Date-TransDate),61,(IFGT( (.#Date-TransDate),45,InvoiceTotal, NULL)), NULL)), + (IFGT( (.#Date-TransDate),60,InvoiceTotal,NULL)) + FROM InvoiceHeader RETURN CREATE TEMPORARY VIEW vwAgingTotals (InvoiceTotal,D15,D30,D45,D60,D60plus) + AS SELECT SUM InvoiceTotal=S, SUM D15=S, SUM d30=S, SUM d45=S, SUM d60=S, SUM D60plus=S + FROM vwAgingAR RETURN BROWSE ALL FROM vwAgingTotals Randy Peterson Blue Springs, Missouri > -------- Original Message -------- > Subject: [RBG7-L] - Re: aging report > From: "A. Razzak Memon" <[EMAIL PROTECTED]> > Date: Wed, September 07, 2005 3:10 am > To: [email protected] (RBG7-L Mailing List) > > At 11:32 PM 9/6/2005, Bryan Bornstein wrote: > > >Hello everyone, I am fairly new to R:Base, but I am very impressed > >so far. I am getting along pretty well so far, only I need to add > >an accounts receivable (A/R) aging report to my program. Is there > >a way to take the Date and invoice amount and "move" them over to > >an aging report(such as 0-15 days, 16-30, 31-45, 46-60, over 60)? > > > Bryan, > > First, welcome to the R:BASE World! > > There are many ways to achieve your task of calculating the age > of any invoice. > > The simple and classic way is to define a view (permanent or > temporary) to first calculate the days. > > A very simple example: > > CONNECT RRBYW9 > SET ERROR MESSAGE 2038 OFF > DROP TABLE tTotalDays > SET ERROR MESSAGE 2038 ON > CREATE TEMPORARY VIEW tTotalDays (TransID,InvoiceTotal,Days) + > AS SELECT TransID,InvoiceTotal,(.#DATE-TransDate) + > FROM InvoiceHeader > > BROWSE * FROM tTotalDays > > That will give you the snapshot of Invoice Number (TransID), > Invoice Amount and total days since the invoice date. > > Now based on that temporary view, you can build your logic > of grouping days and total amount, etc. > > There are a few certified accountants on this list who might > be able to help you. Randy Peterson and Myron Finegold, just > to mention a few. > > Very Best R:egards, > > Razzak.
