5 Replies Latest reply: May 19, 2016 3:54 AM by Daniel Mann

# 12 Month Rolling

Hi Guys,

I know that this has been asked many many many times before, but I can't figure out how to do it using a calculation in stead of a single field.

I need to display a 12 month rolling avg for LTIF on a chart, but the following is not working.

Any suggestions where I am going wrong?

rangeavg( above(sum({<[Accident Category]={'Fatal','Permanent Total Disability (PTD)','Permanent Partial Disability (PPD)','Lost Workday Case (LWC)'}>}[Crew])

/

sum([Total Crew Days]*24)

*

1000000),0,12)

• ###### Re: 12 Month Rolling

Have you tried an AsOf table concept, like described here?

The As-Of Table

Calculating rolling n-period totals, averages or other aggregations

• ###### Re: 12 Month Rolling

Can you provide some sample data with the expected output and we might be able to help you out.

Looking at the expression, there seems to be issues with some parenthesis

RangeAvg(Above(Sum({<[Accident Category] = {'Fatal','Permanent Total Disability (PTD)', 'Permanent Partial Disability (PPD)', 'Lost Workday Case (LWC)'}>} Crew) / Sum([Total Crew Days] * 24) * 1000000, 0, 12))

I am assuming that this is the expression you are trying to accumulate

Sum({<[Accident Category] = {'Fatal','Permanent Total Disability (PTD)', 'Permanent Partial Disability (PPD)', 'Lost Workday Case (LWC)'}>} Crew) / Sum([Total Crew Days] * 24) * 1000000

• ###### Re: 12 Month Rolling

Hi Daniel,

You can create a rolling 12 months flag in your master calender, and use the flag to perform calculations, as this will improve the performce as well.

If( \$(TimeField) > addmonths(\$(CurrentDate),-12) and \$(TimeField) <= Today(),1) as _R12

Thanks,

Sangram.

• ###### Re: 12 Month Rolling

Hi Sangram,

Yes

Sum({<[Accident Category] = {'Fatal','Permanent Total Disability (PTD)', 'Permanent Partial Disability (PPD)', 'Lost Workday Case (LWC)'}>} Crew) / Sum([Total Crew Days] * 24) * 1000000

Is the calculation that I want to have a 12 month rolling average of.

I don't have a master calendar.

I am very new to this, and only just grasping the basics.