Daily Sales Outstanding (DSO) calculation as of date

[rajat2392]

Hi All,

I have requirement to calculate DSO, which was simple to do as on date.

So the calculation goes this way.

Outstanding as on Mar, 2018 - 50,000

Sales Mar, 2018 - 20,000

Sales Feb, 2018 - 20,000

Sales Jan, 2018 - 20,000

So, Outstanding can be knocked off completely by summing up sales of Mar, Feb and Jan, 2018 sales.

But as Jan, 2018 sales should not be considered full, as it becomes 60,000...so Avg. sales of Jan will be taken.

Avg. Sales of Jan, 2018 = 20,000/31 (No.of Days) = 645.17

So DSO = No. of Days in Mar, 2018 + No. of Days in Feb, 2018 + (Remaining outstanding / Avg. Sales of Jan, 2018)

               = 31 + 28 + (10,000 / 645.17)

               = 31 + 28 + 15.5

               = 74.5

Now, the challenge comes with As of date.

Requirement is to calculate DSO, as per date selection by user.

So the Outstanding of the selected date, to be compared with the sales happened on selected date and before till the outstanding gets knocked off.

Please give some suggestions how to handle this, either at front-end or in script (which seems unlikely).

Note - I have More than 50 Million records.

Regards

Rajat Arora

[rajat2392]

Attached the updated excel with DSO as of date expected and the calculation for same in Remarks column.

Solution expectation is anyway either purely on back-end or front-end or mix of both worlds.

Really appreciate your support and time.

Best Regards

Rajat Arora

[rajat2392]

Hi Sunny,

Any luck on this?

I am trying n ways but getting stuck always piling up the data.

Have you found any solution to this?

Best Regards

Rajat Arora

[sunny_talwar]

Sorry about that, will check today

[sunny_talwar]

How did you come up with 12.4 for Customer B?

I tried bunch of calculation, but couldn't get close to it... from what I understood, it should be 8000/((6000+4000)/14) which gives me 11.20... but I might have missed the logic somewhere

[rajat2392]

Hi Sunny,

Sorry..My bad.

For 1 Jan, 2018 and for customer B, it will be 25.8.

Calculation goes like this.

8000/((6000+4000)/31) = 24.8

Though, sales doesn't happen in all the days of Dec, but to get the monthly average, sales divided by total 31 days in month.

[sunny_talwar]

This is much more challenging then I imagined... give me some time and I will get back to you...

[rajat2392]

Your efforts are very much appreciated sunny.

Just to update, I have arrived at a solution which does the whole calculation in the script and gives the expected output.

But that logic requires to make a cross join, which increases the data volume heavily.

Though the data in next 2 steps cuts down with some aggregation and where conditions, but it consumes lot of CPU for that many records.

It would be great if you can also suggest some solution which might reduce the load and data volume.