Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rajat2392
Partner - Creator III
Partner - Creator III

Daily Sales Outstanding (DSO) calculation as of date

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

16 Replies
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))))

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
rajat2392
Partner - Creator III
Partner - Creator III
Author

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))))

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
rajat2392
Partner - Creator III
Partner - Creator III
Author

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...

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
rajat2392
Partner - Creator III
Partner - Creator III
Author

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
Partner - Creator III
Partner - Creator III
Author

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