Debtor Days, Day Sales Outstanding, DSO and all that....
I have recently had a challenge to generate a Debtor Days / Days Sales Outstanding Calculation which I thought I would share. In my past life I have encountered a number of ways of calculating some form of Debt outstanding calculation but this particular method I have encountered before but prior to my time as a Qlik Developer. Basically monthly sales are used to 'run down' the Outstanding Balance, Starting with the latest month first, working backwards and each time applying it to the number of days in the month.
Any remainder would then be applied pro rata to the days in that month. Difficult to explain in words so hopefully the diagram below will make things easier to understand.
I had a bit of a look at performing this in a straight table using all kings of above, rangesum and aggr functions but realised it would probably be better performed as part of a script load. (If anyone has found a way of doing this in a chart object please feel free to answer and Ill mark it as correct / Useful as appropriate)
Below is the result once the script has been run. (please note there's a whole other matters to consider such as : What if the debt is 5 months ago? apply a number of days ago rather than the days in the month?)