topic Re: Rolling 3 month calculation for Days Sales Outstanding in App Development

Rolling 3 month calculation for Days Sales Outstanding

Anonymous — Tue, 28 Aug 2018 19:33:31 GMT

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!

Re: Rolling 3 month calculation for Days Sales Outstanding

petter — Wed, 29 Aug 2018 05:27:15 GMT

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...

Re: Rolling 3 month calculation for Days Sales Outstanding

BalaBhaskar_Qlik — Wed, 29 Aug 2018 06:20:12 GMT

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)