Solved

Tracking Outstanding Invoices over Time

  • 21 March 2023
  • 2 replies
  • 66 views

Badge

Hi!

I’d like to create a metric to keep track of the number of outstanding customer invoices over time.

I have an import from the ERP system with the status of every issued invoice, and the following columns:

  • Customer Number
  • Invoice Date
  • Payment Date

The metric should be of dimension Day, and should return the # of outstanding invoices at any given time.

Excel equivalent:
1. Generated a sequence of days ( → Day metric in Pigment)
2. Use sumproduct formula for the aggregation

=SUMPRODUCT((Invoice_Date>=Day)*((Payment_Date>Day))+(Payment_Date=0))


What would be the equivalent formula in Pigment for this?

icon

Best answer by francois 21 March 2023, 09:56

View original

2 replies

Userlevel 6
Badge +14

Hi Oyvind! Happy to see you in the community.

Here’s how I’d do it:

  • first create a count of customer invoices by day (similar to this topic, using BY COUNT as an aggregator)
  • then use CUMULATE on the Month, starting from the last Month of the calendar.

Using CUMULATE from the last month of the calendar will require using the ON operator, followed by a ranking formula, like this for example:

CUMULATE(1, Month ON -RANK(Month.'Start Date'))

In your case, the final formula could look something like this:

CUMULATE(Invoice[BY COUNT: Invoice.Customer, Invoice.Date], Day ON -RANK(Day.'Start Date'))

Let me know if you can make it work!

Badge

Thanks @francois 

Tweaked the proposed solution a bit, using two CUMULATE functions. One to keep track of the total number of issued invoices over time, and a second to subtract the total number of paid invoices over time, taking the delta of the two to track the number of unpaid invoices. 

Reply