[RS]Prompted Moving average

[cognosjon]

Hi,

I have a report requirement whereby users require the ability to select a financial period and then as a secondary prompt option have the ability to select if they wish to view the report data as a 3, 6 or 12 month moving average. The date selected being the end date.

I've been playing around for a couple of hours and with this now and I'm not sure i'm getting my head around it properly.

At present I have the following filter applied to my report

[Date]between(_add_months(?FinanceDate?,?moving?))and ?FinanceDate?

The FinanceDate parameter simply provides a date prompt  whilst the ?moving? parameter is a list with static choices of  -3,-6 and -12 displayed to the users as 3 month, 6 and 12 as you'd probably guessed.

When the filter runs it subtracts the number of months selected in the list prompt from the FinanceDate and then displays all date between this newly calculated date and the original date selected

Whilst it does appear to work , it has however also produced some odd results that I can't explain.

Has anyone else accomplished this, suggest an alternate method or if you could simply help with the expression it would be greatly appreciated.



CognosJon