
Re: Days of Sales Outstanding
Stephen Matthews Nov 27, 2013 10:47 AM (in response to Chris Cammers )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.
RegardsSteve

Rolling.qvw 167.8 K

Re: Days of Sales Outstanding
Stephen Matthews Dec 2, 2013 4:09 AM (in response to Stephen Matthews)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 
Re: Days of Sales Outstanding
Chris Cammers Dec 6, 2013 12:56 AM (in response to Stephen Matthews)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

Re: Re: Days of Sales Outstanding
Stephen Matthews Dec 6, 2013 4:44 AM (in response to Chris Cammers )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
Rolling.qvw 171.5 K


Re: Re: Days of Sales Outstanding
Stephen Matthews Dec 10, 2013 6:14 AM (in response to Chris Cammers )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

