13 Replies Latest reply on Feb 16, 2018 2:44 PM by Aaron Delaup

Calculating DSO from Sales History and current Aging-AR snapshot (Summarized)

I am trying to link a current snapshot of total amount AR due by customer to sales history data in order to calculate a depleting DSO. I am having an issue with not adding the AR total. The amount due is being placed in the table when 1 record is due instead of rolling them all up into 1 sum at the current time. Ideally we would have sales history invoice data by month by customer Jan2017-Jan2018 and then 1 total AR amount due at the end. This would allow us to calculate DSO based on that current total amount due.

• 2. Re: Calculating DSO from Sales History and current Aging-AR snapshot (Summarized)

Thank for your inquiry. When I tried to join them, it compounded my AR amount due. So for every invoiced line record, it multiplied the AR amount due that many times. For example, if I had 3 invoices for May 2017, it then multiplied my amount due by 3 because it assigned that total on all 3 records. To do this in excel, I would normally create 2 pivot tables. One for invoiced sales data across the multiple months. The second of AR amount due by customer ID. I would then go to my sales history pivot and do a lookup on the customer ID and pull in the current amount due into the sales pivot. That is the basic idea of what I am trying to do in Tableau.

• 4. Re: Calculating DSO from Sales History and current Aging-AR snapshot (Summarized)

Customer number is my joined relationship.

• 5. Re: Calculating DSO from Sales History and current Aging-AR snapshot (Summarized)

Can you please rephrase, what are you looking for. Also give few example for correct answers.

Are you looking for something like this?

• 6. Re: Calculating DSO from Sales History and current Aging-AR snapshot (Summarized)

I looked at your data and I think that you should join on customer number and on sales order number. I created a join for you and attached a workbook. It may or may not be correct - you should validate it. Anyway, I first filtered out all of the records with Sales Order Number = Null and then did an Inner Join on Customer Number and Sales Order Number.

• 7. Re: Calculating DSO from Sales History and current Aging-AR snapshot (Summarized)

Arvin, you are having the same issue that I am. For customer 1000, there should be sales numbers in each month, as there is. However there should only be 1 dollar amount for AR amount due. By joinging the 2 sources with customer number, it is compounding the AR amount due for every sales record. With seeing amount due in every month, I know something is not right. There should only be 1 amount due per customer number to go along with sales each month.

The table could look something like this. Please ignore the actual numbers as I just used some for an example.

• 8. Re: Calculating DSO from Sales History and current Aging-AR snapshot (Summarized)

If you see, we are getting only one number for the amount due and we are getting sales number for each month for customer 1000.

Is this you are looking for?

• 9. Re: Calculating DSO from Sales History and current Aging-AR snapshot (Summarized)

Fantastic...that is exactly what I needed. How was that accomplished?

• 10. Re: Calculating DSO from Sales History and current Aging-AR snapshot (Summarized)

you should bring amount due to rows self and covert it to discrete.

PFA workbook

1 of 1 people found this helpful
• 11. Re: Calculating DSO from Sales History and current Aging-AR snapshot (Summarized)

I changed it to discrete. Why am I still getting multiple amounts due?

• 12. Re: Calculating DSO from Sales History and current Aging-AR snapshot (Summarized)

I don't see blending mark on amount due