Skip to main content

Hi, I am building a report for my client. They want to see the AR aging based on invoice date, not due date. For example, the current column shows invoices created during the past 30 days; 1-31 Days column shows invoices created during the past 31-60 days. I copied the standard AR Aging report and edited formulas on it. I used Iif(CDec( DateDiff( 'd', ,ARInvoice.DocDate], Today()) )<61 AND CDec( DateDiff( 'd', ,ARInvoice.DocDate], Today()) )>30,iif(fARInvoice.DocType]='CRM',-1*1ARInvoice.DocBal],]ARInvoice.DocBal]),0). It works for details. However, when it comes to totals, I used =sum(Iif(CDec( DateDiff( 'd', ,ARInvoice.DocDate], Today()) )<61 AND CDec( DateDiff( 'd', ,ARInvoice.DocDate], Today()) )>30,iif(fARInvoice.DocType]='CRM',-1*1ARInvoice.DocBal],]ARInvoice.DocBal]),0)). This sum function only works for invoices don’t have credit memos/partial payments applied. When there is document applied to the invoice, the total doubled (for1 document applied invoice) or trippled (for 2 documents applied invoice). Can anyone help me with the sum function?

 

Hello,

Did you already try changing your Statement Cycle to age based on Document Date instead of Due Date?

 

Hello,

Did you already try changing your Statement Cycle to age based on Document Date instead of Due Date?

 

Hi Laura, no, since the client also need to review the normal AR aging, I don’t want to change the statement cycle parameters.

The AR Aging report is looking at the setting from the statement cycle, but I’ve customized a report in the past and added a parameter on the AR aging report so that you can select if you want it by Due Date or Doc Date when you run it and then just updated the report to look at the parameter instead of the statement cycle

The AR Aging report is looking at the setting from the statement cycle, but I’ve customized a report in the past and added a parameter on the AR aging report so that you can select if you want it by Due Date or Doc Date when you run it and then just updated the report to look at the parameter instead of the statement cycle

Hi Megan, can you share more details about the parameter?

Add a Parameter for aging.  Use U and O as the 2 options so that matches with the existing Acumatica options.

Then, you’ll need to update a number of the existing variables currently in place to replace where it’s using the default value with the new parameter.

 

Add a Parameter for aging.  Use U and O as the 2 options so that matches with the existing Acumatica options.

Then, you’ll need to update a number of the existing variables currently in place to replace where it’s using the default value with the new parameter.

 

Thank you very much, Megan! I will give it a try.

Reply


Terms & ConditionsCookie settings