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:

Debtors Data.JPG

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

Date Time

Preparation

Accepted answers

LordNeilLord

Hey @Sidey1978

No worries, you just need to group by InvoiceNumber

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome

Data Lover

All comments

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

Data Lover

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.

Debtors Data.JPG

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

DebtorDaysOutput.xlsx

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703