Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
Have you tried an AsOf table concept, like described here?
Calculating rolling n-period totals, averages or other aggregations
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
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.
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.