Calculation of debtor days

[Sidey1978]

Hi community!

I've found my self hitting a brick wall and hopefully some one out there can help....

I am trying to automate the calculation of debtor days. I have extracted the data which contains the posting narrative (invoice number), date and amount. Unfortunately the settlement is not always in the correct order and there are occasions where invoices are part settled. Here is an extract of the data:

I guess the first part is to match the Invoice number comment against the settlement of the same number, then work out the number of days?!

I'd be very grateful if anyone could point me in the right direction.

Thanks

Jonathan

[LordNeilLord]

Hey @Sidey1978

Extracting the invoice number from the text sounds like a good start!

You can use the RegEx parse to extract the invoice number (\d+), then sort by invoice number and date ascending. Lastly you can use the multirow formula tool to work out the datetimediff between each timestamp.

If you want to attach some sample data, I can mock up an example for you.

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome

[Sidey1978]

Hey there @LordNeilLord

Thanks so much for the prompt reply.

I took a stab at it, and I think I'm almost there. However, the result of my DateTimeDiff works out the days for each row. But ideally I don't want a calculation where the new invoice follows settlement of the previous one.

So in row 3 above the answer would be null or zero. Same for row 5 etc...

I've added an output of the data before the DateTimeDiff. If you could take a look that would be great!

Cheers

Jonathan

[LordNeilLord · Accepted solution]

Hey @Sidey1978

No worries, you just need to group by InvoiceNumber :)

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome

[Sidey1978]

Hi @LordNeilLord

You sir are an absolute legend!

That's sorted it, and I can roll this out.

Thanks and all the best!

Jonathan