Rolling 3 month calculation for Days Sales Outstanding

[Anonymous]

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

Sum({<[Exec P&L Heading]={"Revenue"},YEAR={'2018'},PERIOD={'<=$(VCurrentMonth)'}>}MonthlyPostAMT)

//Formula for AR for a single month

Sum({<[BalanceSheetHeading]={"Accounts Receivable, net"},YEAR={'2018'},PERIOD={'<=$(VCurrentMonth)'}>}MonthlyBalanceAMT)

also script for the variable VCurrent date is

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

//v_CurrentDate

Any insight would be greatly appreciated.

Thanks!

[petter · Accepted solution]

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.

Sum({<[Exec P&L Heading]={"Revenue"},YEAR={'2018'},PERIOD={'<=$(VCurrentMonth)'}>}MonthlyPostAMT)

Should be:

Sum({<[Exec P&L Heading]={'Revenue'},YEAR={'2018'},PERIOD={"<=$(VCurrentMonth)"}>}MonthlyPostAMT)

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:

LOAD

     .....

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

    .....

Your expression would look more like:

Sum({<[Exec P&L Heading]={'Revenue'},YEAR={'2018'},PERIOD={">$(v3MonthsAgo)<=$(vCurrentMonth)"}>} MonthlyPostAMT)

vCurrentMonth:

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

v3MonthsAgo:

=Date( AddMonths(Today(1),-3) ,'YYYYMM')

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

[balabhaskarqlik]

For previous 3 months try like this:

Sum({<[Exec P&L Heading]={"Revenue"},YEAR={'2018'},PERIOD={">=$(=MonthStart(AddMonths(Max(PERIOD),-3)))<=$(=MonthEnd(AddMonths(Max(PERIOD),-1)))"}>}MonthlyPostAMT)

/

Sum({<[BalanceSheetHeading]={"Accounts Receivable, net"},YEAR={'2018'},PERIOD={">=$(=MonthStart(AddMonths(Max(PERIOD),-3)))<=$(=MonthEnd(AddMonths(Max(PERIOD),-1)))"}>}MonthlyBalanceAMT)