Imagine if you could easily create a Rolling period expression using a Qlik Function i.e.
Rolling(Field to Roll On, Number of Periods to Roll, Weighting, Forwards (1) /Backwards (0), Go beyond selection (0)/Restrict to selection (1))
e.g. Sum({<Rolling(Months, 3, 0, 0, 0)>}Sales) = Sum Sales rolled backwards on the field Months for 3 periods, no weighting, with results not restricted to date selections. i.e. if dates selected = 2020 Calendar Year, the results for January will include November and December 2019.
e.g. avg({<Rolling(Days, 60, 50, 0, 0)>} Incidents) = Average Incidents rolled backwards on the field Days for 60 days with a weighting of the most recent date being 50% more important than the start date, scaled linearly over the rolling period.
Benefits:
- Prevent having to create multiple "as if" fields - which means that our apps will be leaner and faster.
- Allows the user to create and compare multiple measures easily e.g. competing moving averages
- Prevents the challenges around only accumulating data selected e.g. if I want to show 12 months rolling, accumulation only accumulates the data selected.