I work for a legal services organisation which uses the countback method of calculating debtor days, taking a month end debt value and subtracting sales month by month until reaching zero. The result is a value in number of days. A fairly succinct description of the methodology can be found here (3rd method listed):
http://declanflood.weebly.com/2/post/2013/02/calculate-your-debtor-days-dso-correctly.html
The countback method is something we're finding difficult to implement in a dynamic QlikView data model without creating separate data tables for all the pre-defined ways the company wishes to see the data - in which case we might as well continue to produce the metric in Excel because it reduces the discovery value of a QlikView app.
In effect, we're trying to conduct a kind of "goal-seek" exercise whereby we want to know how many days' sales backwards we need to count to pay off all our debt. We have both sales and debt fact tables at an invoice level. Does anyone have any experience of implementing something similar using set analysis or other method? I would really appreciate any assistance available.
Many thanks.
