How to make seasonal trendlines in Qlik Sense

Every Friday at Bitmetric we’re posting a new Qlik certification practice question to our LinkedIn company page. Last Friday we asked the following Qlik Data Architect certification practice question about how to make seasonal trendlines:

The correct answer was B!

Trend analysis

Qlik has added Time series decomposition modifier functions to the line chart. This can help to analyze trends over time. The time series decomposition is broken down into three different measures:

• STL_Trend; this will identify a trend, independent of seasonal influences

• STL_Seasonal; this identifies seasonal patterns independent of trend influences

• STL_Residual; This is the value that remains to find the original observed value.

These functions can be found under the modifier within a measure. In the second menu ‘decomposition’, you can find the options.

How do the functions work?

The functions are easy to use. Use the decomposition menu in order to select the wanted decomposition and analyze the data. In other situations it could help to insert the formula by yourself.

The STL functions takes four parameters:

– Expression: The aggregation you would like; Sum(Passengers) for example

– Period: The seasonal component. For quarter this will be 4; monthly 12 and weeks 52.

– Optional: seasonal_smoother; the amounts of seasons (so when talking about months; fill in an amount of years;) their should be taken into account to analyze the seasonal pattern.

– Optional: trend_smoother; the amount of periods that should be taken into account when analyzing the trend.

In our testcase; the smoothers weren’t used. The algorithm did a decent job by itself.

How to use it?

First you should know that the combined total of the STL functions equals the original expression.

STL_Trend + STL_Seasonal + STL_Residual = Observed Value

In the example below, we used a test-dataset from the Qlik Help Page. We made the calculation for STL_Trend and STL_Seasonal. As you can see the STL_Trend is a more advanced calculation then a regular linear trend line. The trend flattens in circle 1, while in the second circle the trend is positive. It moves with the growth or decline in passenger numbers.

The STL_Seasonal trend gives us a clearer understanding about how this translates to seasonal changes. We can clearly see that there is a certain seasonal trend, with highs and lows. However using this trendline makes it very clear that the seasonal changes growing, translating into a growth of passenger numbers.

Deviations

As we saw in the previous paragraph, the observed value consists of three parameters. The difference between the Trend and the Seasonal calculations and the observed value is called the residual value. This is calculated using STL_Residual. By using the residual function we can determine the deviations of the statistical formulas on the observed value (Sum(Passengers)).

Let’s look to the residual values and set them off to the observed series. With the formula below, we use the residual value to see how much it deviates from the original observed value.

(Sum(Passengers) - STL_Residual(Sum(Passengers), 12)) / Sum(Passengers)

In the resulting line chart below, we have set 95% and 105% reference lines as acceptable deviations. Everything below or over these lines could warrant a look into what has happened to the amount of passengers. Are there for example external factors in play?

As you can see in most months the differences are within a few percentages of the original value. This means that you probably can use this dataset to forecast the amount of passengers for the next few months.

Things to consider when using STL functions

For general time series analysis it’s best to aggregate at the highest level possible. The example dataset is prepped to show case these STL_functions as nice as possible. However, as you all know, in the ‘real world’ you will encounter external influences. To some extend these might be or be not relevant. For example; if your sales are highly influenced by Easter you can get issues. Easter can be in March and in April. This will increase the residual values and make the seasonal aspect harder.

And what about weather? When a company sells ice cream, the amount of sunshine hours during the month will probably impact the sales. So what is the proper conclusion of a sales spike in July?

– In the month July a lot of people will eat ice cream

– Due to the warm weather a lot of people will eat ice cream as July is a ‘warm’ month.

So keep these things in mind when designing the dataset used for this, or determining the factors.

That’s it for this week! See you next Friday?

More from the Bitmetric team