Announcements
cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Rolling 3 month calculation for Days Sales Outstanding

Hi I am looking to calculate rolling average days sales outstanding.  The calculation would normally be:

Avg of previous 3 months AR

Avg of previous 3 months Revenue                                x 360

I am able to the formula to work for a single month but cannot get it to work as a rolling 3 months avg.  Below are the scripts that I am using for AR and revenue

//formula for Revenue for a single month

//Formula for AR for a single month

also script for the variable VCurrent date is

=num(Month(today()),'00')

//v_CurrentDate

Any insight would be greatly appreciated.

Thanks!

1 Solution

Accepted Solutions
Partner - Champion III

It is important that you use the right quote characters for literal strings/constant values and searches which should be single quotes for the former and double quotes for the latter.

Should be:

You are talking about the variable VCurrent month and showing the definition for v_CurrentDate (at least according to your comment) but in your expression you try to use a variable named VCurrentMonth ... is it just some typo?

Previous three months will not work properly in January and February if you reference YEAR and PERIOD. You should use a (calculated) rolling YEARMONTH field that can be created (if not already there) in your load script.

It could look like this:   201801, 201802, 201803 ....

To calculate it you could:

.....

YEAR & Num(PERIOD,'00') AS YEARMONTH,

.....

Your expression would look more like:

vCurrentMonth:

=Date( Today(1) , 'YYYYMM')

v3MonthsAgo:

For the average once you get the sum of the previous three months right you can only divide the sum by three of course...

2 Replies
Partner - Champion III

It is important that you use the right quote characters for literal strings/constant values and searches which should be single quotes for the former and double quotes for the latter.

Should be:

You are talking about the variable VCurrent month and showing the definition for v_CurrentDate (at least according to your comment) but in your expression you try to use a variable named VCurrentMonth ... is it just some typo?

Previous three months will not work properly in January and February if you reference YEAR and PERIOD. You should use a (calculated) rolling YEARMONTH field that can be created (if not already there) in your load script.

It could look like this:   201801, 201802, 201803 ....

To calculate it you could:

.....

YEAR & Num(PERIOD,'00') AS YEARMONTH,

.....

Your expression would look more like:

vCurrentMonth:

=Date( Today(1) , 'YYYYMM')

v3MonthsAgo: