Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

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)

5 Replies
MVP
MVP

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

chauhans85
Esteemed Contributor

Re: 12 Month Rolling

reddys310
Honored Contributor II

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.

Use this in your script:

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


Thanks,

Sangram.

Not applicable

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.

Community Browser