Advanced time series forecast anomaly detection alert example

[Richard_Byard]

We all want smart alerts that tell us when something is unusual where the threshold of an anomaly is not something we have to set in stone when we setup the alerts. The below document explains how you can do this today with Qlik Alerting on Windows, a server side extension (and some experience with R or Python code) and a little bit of creativity with how you setup a measure.

What was used in this example:

• Qlik Sense Enterprise for Windows June 2020
  • SSE version "sse-r-plugin-1.0.0-qlik-oss" with R version 3.4.1
  • A Qlik Sense application - "UK Reported Crime - June 2017 v3"
• Qlik Alerting for Windows Sept 2020 (although any Qlik Alerting version will work)

In this example we show a seasonally adjusted forecasting algorithm (grey), Holt Winters, which is taking all of the data points from the time series and projecting the forecast which can be compared to actuals (black), and new actuals that come into the dataset.

To create an alert from this chart, do the following:

1. Add the measures from the chart
   • [# of Crimes Reported]
     formula: Sum([Crime Counter])
   • [HW Forecast]
     formula: R.ScriptEval('library("forecast");q$rowid <- 1:nrow(q);q2 <- q[order(q$date1),];crime <- ts(q2$timeSeries, start=c($(vMinYear),$(vMinMonth)), frequency=12, end=c($(vMaxYear),$(vMaxMonth)));
     crimeforecasts <- HoltWinters(crime); crimeforecasts2 <- forecast(crimeforecasts, h=$(vForecastPeriods));t1 <- c(crimeforecasts2$fitted, crimeforecasts2$mean, rep(NA,length(q2$timeSeries) - length(crimeforecasts2$fitted) - length(crimeforecasts2$mean)));
     t2 <- data.frame(rowid=q2$rowid,data=t1);t3 <- t2[order(t2$rowid),];t3$data;',[Reported Date] as date1,Sum($(vForecastDateLimits)[Crime Counter]) as timeSeries)
     
     
     
2. Add the [Reporting Date] as the dimension as per the chart.
   Note: you can do both of these steps using the extension if that makes it easier but in this example they are both master item measures.
   
   
3. Create an additional measure to make the condition simple
   
   • Create a measure that check the last date of the actuals (in other words the latest data)
     look for the max date that has an actual value and compare this to the date in the dimension to flag which value in the time series we need to evaluate later.
     for example: the measure I have created is [Last Date Flag]
     if([Reported Date]=date(max(TOTAL aggr(if(sum([Crime Counter])>0,[Reported Date]),[Reported Date]))),1,0)
     
     
4. Create two conditions for your alert
   • The first will compare where the Actual [# of Crimes Reported] are above the [HW Forecast] plus 5%. for example:
     [# of Crimes Reported] is greater than [HW Forecast] with an offset of 5%.
   • a manual value condition on this boolean measure e.g. [Last Date Flag] = 1
     Note: there are many ways to approach the filtering of the date/datetime of the last actuals either in script or with different formulas. This is an illustrative example only.
     
     
5. In this example the final datapoint of the [# of Crimes Reported] is more than the [HW Forecast] plus 5% and the alert will trigger.

Points to consider when using advanced forecasting algorithms.

• The full time series data will need to be returned to Qlik Alerting, you cannot filter the data in Qlik Sense directly.
• Your flag, in script or in an expression, will be used in a condition in Qlik Alerting, this allows us to filter the time series data after is has been fully constructed, allowing the forecasting algorithm all of the data it requires first.
• The alert dataset which you will return will look something like the below. You can see the identified line which will then be the line we evaluate to say whether the condition is true or false.

Additional Assets

• There are attachments below which will allow you to test this scenario yourself.
• You will need to setup R and the SSE if you have not done already. More info on the setup process for the SSE can be found here .
• You can find info on setting up the SSE as a set of services here.