Sign InHelp

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Documents & videos about Qlik Sense.

- Qlik Community
- :
- Qlik Product Forums
- :
- Qlik Sense
- :
- Qlik Sense Enterprise Documents & Videos
- :
- EoY calculation with SET analysis

Options

- Article History
- Subscribe to RSS Feed
- Mark as New
- Mark as Read
- Bookmark
- Subscribe
- Email to a Friend
- Printer Friendly Page
- Report Inappropriate Content

radoresky

Contributor

11-05-2018
12:49 PM

EoY calculation with SET analysis

EoY (End-of-Year) value of a particular measure is an extension of a commonly known YtD (Year-to-Date) value. Both are greatly relevant when evaluating business performance. But where producing YtD value is a piece of cake, calculating EoY can be quite a challenge.

Part of EoY will always be a prediction and there is no "right" way to do that, only many ways to get "some" result, some of which might be more precise than others.

When the business (and data) model is not very complicated, I use SET analysis feature to calculate EoY values in the front-end objects.

Some examples are:

**1. Flat monthly average** - good for new measures for which we do not have much history.

Expression example:

rangesum( sum({< [Year]={$(v_MaxYear)}, [Month]={"<$(v_MaxMonth)"} >} [#Quantity]) , count( {< [Year]={$(v_MaxYear)}, [Month]={">=$(v_MaxMonth)"} >} distinct [Month] ) * ( sum( {< [Year]={$(v_MaxYear)}, [Month]={"<$(v_MaxMonth)"} >} total [#Quantity] ) / count( {< [Year]={$(v_MaxYear)}, [Month]={"<$(v_MaxMonth)"} >} distinct total [Month] ) ) )

**2. Adjusted last year** - good for stable measures for which we have solid history (at least one full calendar year) and there is a recurring seasonality.

Expression example:

rangesum( sum({< [Year]={$(v_MaxYear)}, [Month]={"<$(v_MaxMonth)"} >} [#Quantity]) , sum( {< [Year]={$(=v_MaxYear-1)}, [Month]={">=$(v_MaxMonth)"} >} [#Quantity] ) * ( sum( {< [Year]={$(v_MaxYear)}, [Month]={"<$(v_MaxMonth)"} >} total [#Quantity] ) / sum( {< [Year]={$(=v_MaxYear-1)}, [Month]={"<$(v_MaxMonth)"} >} total [#Quantity] ) ) )

**3. ****Estimate by linear trend** - good for unstable measures for which we have some history and where we expect a lot of movement (positive or negative trend).

Expession example:

rangesum( sum({< [Year]={$(v_MaxYear)}, [Month]={"<$(v_MaxMonth)"} >} [#Quantity]) , sum( aggr( LINEST_B( {< [Year]=, [Month]=, [YearMonthID]={">=$(=v_MaxYearMonthID-12)<=$(=v_MaxYearMonthID-1)"} >} TOTAL aggr( sum( {< [Year]=, [Month]=, [YearMonthID]={">=$(=v_MaxYearMonthID-12)<=$(=v_MaxYearMonthID-1)"} >} [#Quantity] ), YearMonthID ) ,aggr( only( {< [Year]=, [Month]=, [YearMonthID]={">=$(=v_MaxYearMonthID-12)<=$(=v_MaxYearMonthID-1)"} >} YearMonthID ), YearMonthID ) ) + LINEST_M( {< [Year]=, [Month]=, [YearMonthID]={">=$(=v_MaxYearMonthID-12)<=$(=v_MaxYearMonthID-1)"} >} TOTAL aggr( sum( {< [Year]=, [Month]=, [YearMonthID]={">=$(=v_MaxYearMonthID-12)<=$(=v_MaxYearMonthID-1)"} >} [#Quantity] ), YearMonthID ) ,aggr( only( {< [Year]=, [Month]=, [YearMonthID]={">=$(=v_MaxYearMonthID-12)<=$(=v_MaxYearMonthID-1)"} >} YearMonthID ), YearMonthID ) ) * only( {< [Year]={$(v_MaxYear)}, [Month]={">=$(v_MaxMonth)"} >} YearMonthID ) , YearMonthID ) ) )

A few notes:

- Main fact table must be usually extended to include future dates for main dimensions. This can negatively influence performance, so it's important to evaluate, which dimensions are relevant for EoY calculation and which are not.
- I usually calculate the YtM (Year-to-Month) value only up to the previous month to one selected (or current), in order to have only complete months as the actual base.
- I suggest leaving all different estimates on the screen for users to compare. It is necessary to note that these estimates should not be taken at face value, because all of them are probably quite (or very) wrong, since predicting the future is a tricky thing...
- Seeing EoY value should be regarded as a start of the analysis not its end. The comparison with planned values and other performance measures should direct users to areas which need more attention and deeper analysis. Thus, I suggest to put EoY values to a starting dashboard and give users further drill-down and progressive disclosure possibilities.
- When the business model behind a particular measure is more complex I usually suggest to use RoY (Rest-of-Year) Budget / Forecast / Plan instead of any estimate and fine-tune the methodology of creating those plans. The expression example would be:
rangesum( sum({< [Year]={$(v_MaxYear)}, [Month]={"<$(v_MaxMonth)"} >} [#Quantity]) , sum({< [Year]={$(v_MaxYear)}, [Month]={">=$(v_MaxMonth)"} >} [#Budget]) )

- There is no reason why one should not use more sophisticated prediction methods (e.g. ARIMA), just that SSE to a statistical engine is needed, which complicates things a bit. Anyway, I definitely suggest checking out the possibilities https://github.com/qlik-oss/server-side-extension

See the attached document for all scripts, expressions and variables used to calculate EoY examples. I'm looking forward to any comments and other techniques to calculate EoY.

Radovan

PS: I explained a bit more about using native Qlik functions for linear regression in this document https://community.qlik.com/t5/Qlik-Sense-Enterprise-Documents/Using-linear-regression-to-enrich-dime...

Version history