Expression for Rolling 12 Month Count ignoring Month Dimension.

[#1]

Hey guys,

I've been working on a table that will be converted into one of our dashboards for some safety statistics and I'm stuck attempting to create a rolling 12 month calculation, I'm sure i've done something similar before but search and previous reports yeild no answers as yet.

I've simplified this a fair bit to get the expression working so I can apply it to other things like TRIFR etc. The basic table structure is essentially Month Year (MMM-YYYY) as a dimension and Count([Injury ID]) as an expression:

Month	# Injuries
Jul-2012	12
Aug-2012	18
Sep-2012	8
Oct-2012	7

Then I need to count those injuries over a 12 month period (The Month of the line and up to 12 months before). Ideally the result will look like:

Month	# Injuries	Rolling 12 Month
Jul-2012	12	12
Aug-2012	18	30
Sep-2012	8	38
Oct-2012	7	45
Nov-2012	6	51
Dec-2012	12	63
Jan-2013	9	72
Feb-2013	9	81
Mar-2013	10	91
Apr-2013	7	98
May-2013	4	102
Jun-2013	9	111
Jul-2013	19	118
Aug-2013	5	105

I believe I need the expression to ignore the Month dimension, and then use set analysis to select where the Month is greater thanor equal to AddMonths(Month, -12) and less than or equal to Month. However I'm not sure how to achieve this.

using TOTAL in my calculation seems to ignore my set analysis: ie:

=Count(TOTAL {<[MTH YR]={'>=$(AddMonths([MTH YR], -12))'}>} [Injury ID])

same result with =Count(TOTAL <[MTH YR]> {<[MTH YR]={'>=$(AddMonths([MTH YR], -12))'}>} [Injury ID])

Any help would be appreciated, thankyou.

[Anonymous]

Please look into the attached file.

I think this is required by you.

Regards

Nitin

[#3]

Thanks for replying nitin, while this works on an unfiltered report I've avoided using Above as the dashboard will likely be filtered down to the range of several months by the users and Above will give what they perceive to be incorrect results in that situation.