Smoothing, seasonality and concentration functions

Smooth a noisy series, find how strong the current season is, and measure how concentrated your suppliers or a capital event are.

Syntax

Write the function's name and its arguments. A series is passed as one text of numbers separated by commas, oldest first, such as "100,120,90".

SMA(values, n)
EWMA(values, alpha)
SEASONAL_INDEX(values, period)
SUPPLIER_HHI(shares)
CAPITAL_SENSITIVITY_INDEX(ev_delta_abs, total_ev)

Parameters and arguments

Arguments are written in brackets, separated by commas. An argument in square brackets is optional.

  • SMA

    values

    Required: Yes
  • n

    Required: Yes
  • EWMA

    values

    Required: Yes
  • alpha

    Required: Yes
  • SEASONAL_INDEX

    values

    Required: Yes
  • period

    Required: Yes
  • SUPPLIER_HHI

    shares

    Required: Yes
  • CAPITAL_SENSITIVITY_INDEX

    ev_delta_abs

    Required: Yes
  • total_ev

    Required: Yes

Examples

Six months of sales: the average of the latest three, and an average that weights recent months at 30 per cent.

Return type and behaviour

Each function returns one value. The table shows its type, what it does and one way to write it; the examples use the functions in realistic statements.

FunctionReturnsBehaviourForm
SMAdecimalThe simple moving average of the latest n valuesSMA("100,120,90,130,110,140", 3)
EWMAdecimalThe exponentially weighted moving average: recent values count moreEWMA("100,120,90,130,110,140", 0.3)
SEASONAL_INDEXdecimalHow the latest value's season compares with the average: 1 is average, above 1 is a strong seasonSEASONAL_INDEX("100,80,90,130,110,90,100,140", "quarter")
SUPPLIER_HHIdecimalThe Herfindahl index of concentration on a 0 to 10,000 scaleSUPPLIER_HHI("50,30,20")
CAPITAL_SENSITIVITY_INDEXdecimalThe fraction of enterprise value moved by a capital eventCAPITAL_SENSITIVITY_INDEX(150000, 1000000)
Errors and exceptions
ErrorWhat happenedHow to recover
HTQL-5001The function was given the wrong number of arguments. The message names the function and the countsGive it the arguments listed under Parameters
HTQL-1004 Unsupported functionThe name is not a function this engine runsCheck the name and its spelling against this entry
HTQL-2001An argument is out of range, such as an alpha of 0, too few values, an unknown period or a negative sharePass values inside the range listed under Limitations
HTQL-8003A value in the text is not a number (#VALUE!)Pass numbers separated by commas
HTQL-5004 Not enough paramsAn argument is missingGive every argument listed
Limitations
  • SMA needs at least n values. SEASONAL_INDEX needs one full cycle of values and an average that is not 0. EWMA needs at least one value.
  • The series is one text. A list or a column is not accepted: build the text of numbers first.
  • SUPPLIER_HHI refuses a negative share. Shares are read as fractions when every one is 1 or less, otherwise as percentages.
  • SEASONAL_INDEX answers for the latest value's position in the cycle, counting from the first value as position one.
  • A finance function's result cannot be an argument of another finance function, so type the figure in or name it with LET. The result can be compared in IF and used in ADD, SUBTRACT, MULTIPLY and DIVIDE.
Permissions and security

These functions read nothing from your tables and write nothing.

When a function is applied to a column or a variable, the statement that reads the data needs read access, as its own entry says.

Best practices
  • List the series oldest first, so the latest value is last.
  • Use a moving average to smooth a trend and a seasonal index to adjust for the time of year, then combine them in your own plan.