Skip to main content

Hi everyone,

I’m trying to determine whether each active customer received their invoice for each month.

I have two tables, both having the dimensions month and customer ID:

  • An "Activity" table, which has one row per customer per month, indicating whether the customer was active. If the client was not active on a specific month, he will just not appear in the table for this month

  • An "Invoices" table, which lists all the invoices sent.

Thanks in advance for your help!

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)nremove firstnonblank: Invoices]
     
  • Check invoicing number
    Type = Text
    Formula = if(Activity.'Customer ID' = Invoices.'Customer ID' and Activity.Month = Invoices.Month, Invoices.’Invoice number’)vremove firstnonblank: Invoices]

 

Later on, you can create KPI metrics:

  • Count of missing invoices by Customer and Month
    Activity.'Customer ID'iexclude: Activity.'Check invoicing']Cby 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'vexclude: Activity.'Check invoicing']'by count: Activity.'Customer ID',Activity.Month]Dremove : Customer, Month] / Activity.'Customer ID'vby count: Activity.'Customer ID',Activity.Month]Dremove : 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.  

Reply


Ask your question here!

Terms & ConditionsCookie settings