Analytics Software

Hello All -

Today I have a puzzler regarding rolling period calculations. I have created a calculation called "4 Week Moving Average Sales", this calc using the function AGO to do something like the following:

(AGO(Invoice.Sales, Calendar Week, 3) + AGO(Invoice.Sales, Calendar Week, 2) + AGO(Invoice.Sales, Calendar Week, 1) + AGO(Invoice.Sales, Calendar Week, 0))/IfNull(4,0)

Calendar Week is a level with the key formatted as YYYY-WW

This works great except that I have blank values in a few weeks, and while the blanks are inconsistent they are around year boundaries. For example I have a blank in 2008-52, and Also have blanks from 2010-01 to 2010-03. I have speculated that Sales might have null values that are undermining this calculation, but that theory seems remote as Invoice.Sales is itself a calculated column and there are no nulls for any period in the range of my report. A second theory was that Oracle may not find the correct "ago" period when crossing a year boundary. Again this seems unlikely as the behavior is not identical between 2008 and 2009 and 2009 and 2010.

Has anyone encountered something like this in a rolling period calculation, and have you solved it? I'd appreciate any ideas.

Thanks,

Mac