Customer Days to Pay

We’ve created a query that calculates average days for customers to pay us. However, does anyone have an idea of how to revise this formula to exclude any result (payment date - invoice date) < 5?

image

We’d like to exclude results < 5 because we consider these anomalies.

Thanks,

Mike

Excluded all together? Would forcing those to be 5 days be okay?

Maybe something like

SUM((case when convert(int, daydiff(day, InvcHead.InvoiceDate, CashDtl.TranDate)) > 5 THEN convert(int, daydiff(day, InvcHead.InvoiceDate, CashDtl.TranDate)) ELSE 5 END)) / COUNT(Customer.CustID)

To truly exclude them, the ELSE 5 Would be ELSE 0, and the divisor would need to be the count of the records > 5 days. Maybe replace the COUNT(…) with

SUM((CASE WHEN convert(int, daydiff(day, InvcHead.InvoiceDate, CashDtl.TranDate)) > 5 THEN 1 ELSE 0 END))

You should be able to use the Avg function which will ignore nulls:

AVG(case when convert(int, datediff(day, InvcHead.InvoiceDate, CashDtl.TranDate)) < 5 then null else convert(int, datediff(day, InvcHead.Invoicedate, CashDtl.TranDate)) end)
1 Like

Calvin, that worked. The only thing I tweaked was to use Tyler’s suggestion of “then null” instead of “else 5” or “else 0”. Thanks guys!

Slight complication, wondering if anyone has a quick fix:

sum((case when convert(int, datediff(day, InvcHead.InvoiceDate, CashDtl.TranDate)) > 5 then convert(int, datediff(day, InvcHead.InvoiceDate, CashDtl.TranDate)) else null end))/count(Customer.CustID)

Above formula works, but “/count(Customer.CustlD)” is still counting cases where days to pay is < 5. So, for example, if one customer had 2 invoices, and paid 1 of those in 2 days, and the other in 8 days, the formula is resulting in an average of 4, (8/2 invoices) instead of 8.

Any thoughts?

Hey Tyler, actually, swapping sum with avg fixed this problem. Thanks again!

Is it possible for you to share an export of the BAQ on this thread? Much appreciated if you can.
Thanks!