Days of Sales Outstanding

[chriscammers]

I am working on an application for Accounts Recievable. The last chart I need to produce is a pivot table showing Month across the top, year down the left and Monthly Days Of Sales Outstanding(DSO) in the body. I've tried a bunch of variations but I cannot get the DSO calculation to work.

Here is what they want

     (Current AR Balance/Average of Last Two Months of Invoices)*30

We can speak to the validity of the measure later, I'm having trouble with the "Last Two Months of Invoices" I've tried set analysis and aggr in many different combinatons but not had any luck as long as the Month is in the chart. When I calculate it in a text box it works fine. I'm sure aggr and set analysis are the key to it but I need some suggestions to get it right.

Thanks

Chris

[#2 · Accepted solution]

Hi Chris

That's a really difficult one you've got there.  I spent quite a while on the same issue a couple of years ago - I've plug a simplified version of my formula into the attached qvw.

Two things to mention; firstly, the file includes a straight table which performs the simple formula you are after but not in the format.  This table is used to probe the script based solution which uses a Prev_Value field in the script.  However, this previous value field depends on aggregated data.  The final table is the solution you want.  This does not require aggregation or sorting, it performs all the work in the live qvw (which is more onerous on processing).

Secondly, you may notice a slight difference between the two tables (see 2013, Month 6).  This is because there is a slight error in the script logic and I don't have the time to have the qvw work in both methods - sorry!!  Basically the error arises from as the Peek Prev_Value only picks up one 2013 Month 5 where I put two records into the inline table to ensure multiple records work with the formula based solution.

I hope this solves your issue.

Regards

Steve

[#3]

Hi Chris

Did that file do the job/demonstrate enough to be able to fulfill your requirement?  Let me know how you get on or if you need any additional help.

Steve

[chriscammers]

Steve,

You have given me a very good fish. Thank you very much!

In the interest of learning how to fish could you provide some explanation of the formula solution. There are several functions you are using in there that I have not used before and with everything going on in there when I tried to build it up step by step I ended up with bad formulas.

Thanks again

Chris

[#5]

Hi Chris

I've added some commentary to the formula in the 'Formula Based Solution' table.  This should explain what I am doing.  Upon reflection, I realised I was approaching the formula differently for the situation when in column 1 (i.e. Month 1) to the other months.  Although a different formula is needed, the technical approach was different.

Therefore I have added a 2nd formula based solution table which is more efficient that the previous formula.  The FIRST function does not reference previous cells directly, it recalculates what should be in that cell given the formula, thus calculating for last month does not pick up the previous cell figure (which include the previous month and previous month +1), it simply calculate Sum(Value) for the previous month - hence my 1st iteration of the formula was not efficient.

Anyway, I'll try not to bore you with details.  Have a look at the commentary and hopefully this should help equip you going forward.  Also, the QV help is very good for breaking down what each function does, with some additonal functions you may find helpful going forward that are not included in this model.

Let me know if this has been helpful or if you need a few other pointers.

All the best

Steve

[#6]

Hi Chris

Did this make sense?  Let me know if you want me to expand on anything.

Let me know if it was helpful or not.

All the best with your developing.

Steve