Advanced Functions – Exhaustive DSO using dollar-sign expansion with parameters

As everybody probably knows by now, the book “QlikView 11 for Developers” by MIguel Garcia and Barry Harmsen is an excellent resource.  Other than the cover photo and the title that could lead someone to think developing Qlik involves hardcore programming; and the space wasted on showing the pie, grid, and funnel chart, I enjoyed reading what is to date the best didactic material about Qlik.

Even though I’ve worked with Qlik for eight years, I learned a few tidbits from the book.  For example, I hadn’t noticed that you could add parameters to variables, and with the help of dollar-sign expansion, pass parameters to those variables.

I avoid using techniques in Qlik that make it feel like you’re programming because I believe Qlik should be as simple and as open as possible for business users.  However, sometimes the benefit of using advanced techniques is too great to ignore.

This is true for the case of calculating Days Sales Outstanding (DSO) using the exhaustive method.  DSO is an insightful performance indicator that informs users how well their customers are paying. According to Wikipedia, the quick method for calculating DSO is as follows:

DSO ratio = accounts receivable / average sales per day,
or DSO ratio = accounts receivable / (annual sales / 365 days)

The result is the number of days you can expect to wait for your customer to pay after being invoiced.  For example, if the average sales per day of a customer is $5 and their accounts receivable (A/R) balance is $200, then the DSO, or the average number of days it takes for the customer to pay, is 40 days.

The above calculation is simple, but be aware that there is another method to calculate DSO that is not so simple and could cause delays in your Qlik project if you didn’t know enough to even ask the business users how they calculate DSO.

The other method is called the exhaustive method and it involves going back a month at a time to subtract real monthly sales from the current A/R balance until the A/R balance reaches zero.  For example, if our customer has an A/R balance of $200 at the end of last month, and the sales of the past 3 months of that customer are as follows:

Feb 2014 Sales - $100
Jan 2014 Sales - $75
Dec 2013 Sales - $75

Then the DSO using the exhaustive method would be as follows:

$200 (A/R) - $100 (Feb 2014 Sales) = $100, which results in 30 days of DSO
+
$100 (Remaining A/R) - $75 (Jan 2014 Sales) = $25, which results in another 30 days of DSO
+
$25 (Remaining A/R) - $75 (Dec 2014 Sales) = -$50, which since the remaining A/R is below zero, the resulting number of additional days of DSO is 25 divided by 75 multiplied by 30,or 10 days
=
70 days

In Qlik, this means adding up multiple sum expressions that differ only slightly, so using a variable with a parameter can make creating and maintaining the calculation easier.  A parameter in a variable is defined using $1, $2, $3, etc.  For example, a variable called vSales that is defined as follows:

sum(Sales)/$1

and called using the following dollar-sign expression:

$(vSales(1000))

would result in the sum of sales being divided by one thousand.

In our example of calculating DSO using the exhaustive method, we define the following variable with a parameter that controls the range of months that are used to calculate sales and subtract it from the A/R balance:

IF(
Sum({$<Year=, Month=, MonthYear={'$(=MonthName(max(MonthYear),-1))'}>}[A/R Balance])<=0
 or Round(Sum({$<Year=, Month=, MonthYear={'$(=MonthName(max(MonthYear),-1))'}>}[A/R Balance])
-Sum({$<Year=, Month=, MonthYear={">=$(=AddMonths(Max(MonthYear),$1+1))<=$(=AddMonths(Max(MonthYear),-1))"}>}[Sales w/ VAT]))<=0
 ,0, 
 IF(Round(Sum({$<Year=, Month=, MonthYear={'$(=MonthName(max(MonthYear),-1))'}>}[A/R Balance]) 
 -Sum({$<Year=, Month=, MonthYear={">=$(=AddMonths(Max(MonthYear),$1))<=$(=AddMonths(Max(MonthYear),-1))"}>}[Sales w/ VAT]))>0 
 ,30, 
 30*(Sum({$<Year=, Month=, MonthYear={'$(=MonthName(max(MonthYear),-1))'}>}[A/R Balance]) -Sum({$<Year=, Month=, MonthYear={">=$(=AddMonths(Max(MonthYear),$1+1))<=$(=AddMonths(Max(MonthYear),-1))"}>}[Sales w/ VAT])) /Sum({$<Year=, Month=, MonthYear={'$(=MonthName(max(MonthYear),$1))'}>}[Sales w/ VAT])
 )
)

Therefore, our resulting expression in our Qlik table is the following expression.

//Month -1
IF(Sum({$<Year=, Month=, MonthYear={'$(=MonthName(max(MonthYear),-1))'}>}[A/R Balance])<=0
,0,
 IF(Round (Sum({$<Year=, Month=, MonthYear={'$(=MonthName(max(MonthYear),-1))'}>}[A/R Balance])
 -Sum({$<Year=, Month=, MonthYear={'$(=MonthName(max(MonthYear),-1))'}>} [Sales w/ VAT]))>0
 ,30, 
 30*(Sum({$<Year=, Month=, MonthYear={'$(=MonthName(max(MonthYear),-1))'}>}[A/R Balance]))
 /Sum({$<Year=, Month=, MonthYear={'$(=MonthName(max(MonthYear),-1))'}>}[Sales w/ VAT])
 )
)
+ $(vDSOExhautive(-2))
+ $(vDSOExhautive(-3))
+ $(vDSOExhautive(-4))
+ $(vDSOExhautive(-5))
+ $(vDSOExhautive(-6))
+ $(vDSOExhautive(-7))
+ $(vDSOExhautive(-8))
+ $(vDSOExhautive(-9))
+ $(vDSOExhautive(-10))
+ $(vDSOExhautive(-11))
+ $(vDSOExhautive(-12))
+ $(vDSOExhautive(-13))

Please note that the set analysis in the first month is different enough to warrant being calculated separately.  Also, Qlik will look as far back as thirteen months of sales to eliminate the A/R balance.  Finally, this expression may take more than a few seconds to calculate in some data models.

You can download a sample Qlik application that includes the previous DSO example from my Downloads page.  As always I would be happy to hear of any comment, improvement or questions you may have.

Hope to see you around,

Karl

Author: Karl Pover

Owner of Evolution Consulting, which provides Qlik consulting services throughout Mexico. Author of "Learning QlikView Data Visualization" and "Mastering QlikView Data Visualization." Qlik Luminary since 2014. View all posts by Karl Pover