Hi,
I'm setting up a GridView that will look similar to the Overdue Receivables Report in AR.

This is the formula that I have right now in the calculated column for the "Current" amount:

if ({Due Date}>={As Of Date},{Current Amt},0)

I have made different kinds of changes to the formula but it does not have the same total as the "Current" column from the AR report.

Is this a wrong formula to use for that column or am I missing something?

Miriam:
The formula looks reasonable. Where is the "As of" date coming from? Put it into a column on the report just so you can verify it's what you think it is. Remember that, unlike the true Overdue receivables report, you can't back-date this GV report.

There's an easier way, and it doesn't involve GridView. Run Calculate Customer Aging, and then create a Filter to select only those customers with a balance. Choose the Aging buckets for the Grid when that Filter is selected. Voila - a one line per customer Grid that shows the same as the Overdue Receivables Summary Report.

This is a good idea except that we need additional columns from JC.

Why can't we set a filter to the "Current" column?

You can.

Okay, this is the formula that we have for that column:

if ({Due Date}>={As Of Date},{Current Amt},0)

Is there something wrong with it? or why would it not show the same amount as the AR report?

Have you run "Calculate Customer Aging" in Adagio Receivables with the same date as the report was run with? That's the function that puts the number in that field.

Miriam:
In addition to my earlier (unanswered) question, I would ask what figure does show? And add another column that shows the calculated number of days between the "As of" date and the Due Date.

Hi Bruce,

The as of date is this formula:

QUERYDATE("As Of") and the table is A/R Transactions.

I added the columns the way you suggested but it's still the same.

Which figure are you referring to?

Hi Miriam,

The easiest way to create an 'aging' inquiry in GridView on the A/R Transactions file is to first create a calculated column that determines the number of days 'aged' (by document date) or 'overdue' (by due date) and then use that for your Current/30/60/90 columns. For the Due date, the formula 'Days Overdue' would be: Today() - {Due date} You can change the value of 'Today' by editing the Run Date, so that you can age as of a desired date.

Instead of Today(), you could also use the 'As of' querydate result.

Then create your aging columns as you would expect:
Current: IF({Days overdue} <=0, {Current amount} , 0)
1-30: IF( AND(Days overdue >0, {Days overdue} <= 30) , {Current amount} , 0)
and so on.

This inquiry against the aged Due Date will match the results of the Overdue Receivables report.
Note that it will not match the Aged Trial Balance report because that report ages against the Docuemnt Date, and also there is a possibility that the report date could be backdated earlier than the last purge date, meaning historical transactions could be listed on the report - and not your GridVew inquiry.