By using this website, you agree to our Terms of Use (click here)
Hi,
I would like to create a generic inquiry to display the AR Aging summary by aging bucket:
| Current | 1-30 | 30-60 | 60-90 | Over 90 | Balance |
| 600.00 | 500.00 | 400.00 | 300.00 | 100.00 | 1,900.00 |
| 32% | 26% | 21% | 16% | 5% |
|
Any ideas on where to start?
Thanks,
John
What version are you using John? There is an example included in versions 2017 R2 and 2018 R1. The Inquiry Title is DB-ARCustSummary
To get a detail listing instead of a summary, copy the GI into a new one, remove the grouping clause, and sort by customer and ref number. You will need to add a few new columns to the results grid to get detail on the individual invoices but that should get you started on the right path.
Cheers!
Hi Shawn,
I was hoping just to get the totals of the Aging Buckets.
You can use the Generic Inquiry that Shawn mentioned, add an Aging Bucket column, and put a Pivot Table on top. It's easier to show in a video so I recorded one.
Here is the formula that I used for the Aging Bucket column in the video:
=Switch(DateDiff('d',[ARInvoice.DueDate],Today())<31,'1-30',DateDiff('d',[ARInvoice.DueDate],Today())<61,'30-60',DateDiff('d',[ARInvoice.DueDate],Today())<91,'60-90','Over 90')
How would I go about turning this inquiry into a dashboard widget? I would like to create a chart showing the totals of each period, but I do not know how to configure the chart in order to accomplish that.
Try something like this:
Thanks Tim, this was a lot of help. However, instead of subtracting the credit memos, they are just being ignored in my inquiry. I copied the formulas exactly as they appear in your video, so I am pretty confused as to why it is not working properly.
Update to my issue. The balance column is subtracting the memos, but the bucket columns are not. Was there a step that I possibly missed to avoid this issue?
Sorry for all the posts, but I determined that it is because my credit memos do not have a due date, so they cannot fall under any of the categories and are just omitted. Is there anyway to get around this issue?
Jake, you can always set the due date to current if it is blank. To do this, wrap the formula Tim provided on May 4 with an appropriately configured if statement.
You will need to adjust your selection criteria to ensure you don't exclude transactions without a due date.
Online Members
