12 Month Rolling

[#1]

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)

[swuehl]

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

The As-Of Table

Calculating rolling n-period totals, averages or other aggregations

[sunny_talwar]

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

[SunilChauhan]

Rolling 12 months cumulative

[reddy-s]

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.

Use this in your script:

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

Thanks,

Sangram.

[#6]

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.