Qlik Community

Qlik Sense Enterprise Documents

Documents for Qlik Sense related information.

EoY calculation with SET analysis

radoresky
Contributor

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.

app_screenshot.PNG

 

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...

Attachments
Version history
Revision #:
2 of 2
Last update:
Thursday
Updated by:
 
Contributors