Hello Everyone,
I would like to know if there is a way to create a BAQ AR Aging other than the standard Aged Report from Epicor.
In other ERP Systems I would take the invoice date and subtract the current date and if the number is under 30 days then use the number to give me the invoiced amount and create the different buckets.
Is it possible to create the buckets? I tried the logic mentioned above but it didn’t work.
Thanks in advance.
Miguel
You can make a calculated field for the “bucket number”.
Is your end goal kind of a pivot chart? Just group by Customer then “Bucket”. The report wouldn’t show the actual records (the detail section as CR called it), but just show the footer, with each column showsing the sum partitioned over the bucket number.
My goal is to create an aging on invoices paid by customer. So it would display as shown below:
Example
1-30 days 30-45 days 46-65 days over 66 days
Customer Name $10,000 $15,000 $20,000 $100,000
In addition, how do I create the calculation, as the one I created didn’t work.
Thank you.
Miguel
for this a lot of times I will use case statements for each bucket and that seems to work pretty well. Examples are rough examples not exact
current would be:
CASE WHEN InvoiceDate < Today + 30 days THEN SUM(invoiceamt) END
30 past duewould be:
CASE WHEN Invoice Date >= Today + 30 days AND InvoiceDate < Today + 60 days THEN SUM(invoiceamt) END
You can create different aging formats under Aging report format for both AR and AP.
Vinay Kamboj
Hello Joshua,
I unfortunately don’t have the option for case statements in my calculation in Epicor 9… Although I put in calculation without the case statement the query bombed out.
Thank you.
Miguel
Hello Vinay,
I am sure in E10 it works fine. I unfortunately am on Epicor 9.
Thank you.
Miguel
Never heard of this not being able to be done. Are you sure its not due to the structure of your BAQ? could you try on a simpler BAQ test?
In progress instead of case use an If statement
Would it be an iif(,,) statement?
Like iif(Invoice Date >= Today + 30 days AND InvoiceDate < Today + 60 days, SUM(invoiceamt), 0)
Also, don’t CASE statements need an ELSE?
Like
(CASE WHEN Invoice Date >= Today + 30 days AND InvoiceDate < Today + 60 days THEN SUM(invoiceamt) ELSE 0 END)
Hi Miguel,
FastClose can also be an easy & great solution for your request. Its working on Epicor 9 and 10 and they provide out of the box AR aging template that allow you to setup the brackets you want, see below :
You can find more information about Fastclose in our web site Fast Close | PSE
I hope this can be on any help. Don’t hesitate to contact us if you need more information on this.
Thierry
