How to check if each active customer received an invoice this month?

By mentioning ‘table’ in the above description, do you mean a dimension list or transaction list?

​@pigment_enthusiast 

Transaction List

Hi Maxence,

Hope you’re doing well!

If you have two transactional lists, I’d suggest you do the check in your Activity Transational List.

For each Month your customer had been active, you need a corresponding Invoice.

I made a short reproduction:

Basically, I’ve just compared Customer ID and Month, and when both properties match, I return either true or the value of my choice, could be any Invoice list property you have (Invoice number, Invoice total, Invoice status...)

So in the Activity Transactional List, I created new properties:

• Check invoicing
  Type = Boolean
  Formula = if(Activity.'Customer ID' = Invoices.'Customer ID' and Activity.Month = Invoices.Month, true)[remove firstnonblank: Invoices]
   
• Check invoicing number
  Type = Text
  Formula = if(Activity.'Customer ID' = Invoices.'Customer ID' and Activity.Month = Invoices.Month, Invoices.’Invoice number’)[remove firstnonblank: Invoices]

Later on, you can create KPI metrics:

• Count of missing invoices by Customer and Month
  Activity.'Customer ID'[exclude: Activity.'Check invoicing'][by count: Activity.'Customer ID',Activity.Month]
  This would tell you exactly the Months where you have invoices missing, by Customer.
   

• % of missing invoices
  Activity.'Customer ID'[exclude: Activity.'Check invoicing'][by count: Activity.'Customer ID',Activity.Month][remove : Customer, Month] / Activity.'Customer ID'[by count: Activity.'Customer ID',Activity.Month][remove : Customer, Month]
  This would allow you to monitor that the missing invoices rate is reducing.

Hope this helps.

Best,

Hi Benoit, 

Thank you very much for your tips!
Unfortunately both transaction lists are very heavy (13M lines and +450k lines/Month) therefore the formula can not run. Here is the error message :
The formula reached 59 T cells during one of its execution steps, exceeding the maximum size allowed (1 G)

Do you have any tips on how i can improve it? 

Thanks

What happens if you try the operation in a metric ?

Open the formula playground in automatic mode and adjust this formula to make it pass:

if(Activity.'Customer ID' = Invoices.'Customer ID' and Activity.Month = Invoices.Month, true)[remove firstnonblank: Invoices][by firstnonblank: Activity.'Customer ID', Activity.Month]

Let me know.

Best,

I’m encountering the exact same issue.

I think i’ll try to add an extra property to each transacation lists, which will be a unique key based on the customer ID and the month. 
I’ll then see if i can retrieve this id in both table 

The formula might be lighter this way.