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

[Anil_Babu_Samineni]

I think, AS far i understand your request looks this

In script, You can use lile this?

Load Datefield,

Num(weekday(Datefield)) as Days,

Num(Month(Datefield)) as MonthNo,

Year(Datefield) as Year

Resident Table;

And Formulae for DSO In Front-End

((Count({<MonthNo = {3}, Year = {$(=Max(Year))}>} Days) + (Count({<MonthNo = {2}, Year = {$(=Max(Year))}>} Days)) + (Sum({<MonthNo -= {3}, Year -= {$(=Max(Year))}>} Sales) / (Sum({<MonthNo = {1}, Year={$(=Max(Year))}>} Sales)/Count({<MonthNo = {1}, Year={$(=Max(Year))}>} Days))))

[rajat2392]

Hi Anil,

Thanks for the reply.

Scenario I have given was just an example, outstanding can span over 2, 3, 4 or more months..depends on the sales.

Regards

Rajat Arora

[Anil_Babu_Samineni]

Why can't you make it in dynamic?

((Count({<MonthNo = {$(=Max(MonthNo))}, Year = {$(=Max(Year))}>} Days) + (Count({<MonthNo = {$(=AddMonths(Max(MonthNo, -1)))}, Year = {$(=Max(Year))}>} Days)) + (Sum({<MonthNo -= {$(=Max(MonthNo))}, Year -= {$(=Max(Year))}>} Sales) / (Sum({<MonthNo = {$(=YearStart(Max(Montho)))}, Year={$(=Max(Year))}>} Sales)/Count({<MonthNo = {$(=YearStart(Max(Montho)))}, Year={$(=Max(Year))}>} Days))))

[rajat2392]

What if the outstanding gets cleared with the sales of same month itself?

Your expression will count all the days of the month and then add days in previous month as well.

So it need to be checked first that till what date outstanding can be cleared with sales and then calculate no. of days.

Regards

Rajat Arora

[Anil_Babu_Samineni]

Over to you can use Implicit https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/ChartFunctions/SetAnalys...

[rajat2392]

Sorry Anil, I guess my requirement is not clear to you.

Using set analysis might solve it but logic to make it happen is very important here.

Outstanding might get cancelled with sales of a month or 2 or more.

A expression to check that first need to be designed and should be iterative to span over months till outstanding gets cleared.

Upon finding the range of months that cancels outstanding, no. of days to be calculated as mentioned in my example.

Hope this throws more light on my example.

[sunny_talwar]

Would you be able to share two different set of examples in the format the data is available in to create a sample and test it out? There is not a straight forward expression we can provide you... it will need to be tested out with at least couple (if not more) examples.

Best,

Sunny

[rajat2392]

Hi Sunny,

Attaching an excel file with sample data for 2 customers.

Hope there must be some solution to this.

Regards

Rajat Arora

[sunny_talwar]

One more thing... what is the expected output that you are hoping to get? Also, you want this purely from front end, or back end changes will work also...

Note: I am not sure if all front end will work... but just wanted to understand the expectations