Skip to main content

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?

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(InvoiceoBY COUNT: Invoice.Customer, Invoice.Date], Day ON -RANK(Day.'Start Date'))

Let me know if you can make it work!

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


Ask your question here!

Terms & ConditionsCookie settings