Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Partner
Partner

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

Re: Daily Sales Outstanding (DSO) calculation as of date

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

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

Re: Daily Sales Outstanding (DSO) calculation as of date

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

Re: Daily Sales Outstanding (DSO) calculation as of date

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

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

Re: Daily Sales Outstanding (DSO) calculation as of date

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

Re: Daily Sales Outstanding (DSO) calculation as of date

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

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

Re: Daily Sales Outstanding (DSO) calculation as of date

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.

Re: Daily Sales Outstanding (DSO) calculation as of date

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

Partner
Partner

Re: Daily Sales Outstanding (DSO) calculation as of date

Hi Sunny,

Attaching an excel file with sample data for 2 customers.

Hope there must be some solution to this.

Regards

Rajat Arora

Re: Daily Sales Outstanding (DSO) calculation as of date

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