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
Required: Yesvalues
Required: YesnEWMA
Required: Yesvalues
Required: YesalphaSEASONAL_INDEX
Required: Yesvalues
Required: YesperiodSUPPLIER_HHI
Required: YessharesCAPITAL_SENSITIVITY_INDEX
Required: Yesev_delta_abs
Required: Yestotal_ev
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.
| Function | Returns | Behaviour | Form |
|---|---|---|---|
SMA | decimal | The simple moving average of the latest n values | SMA("100,120,90,130,110,140", 3) |
EWMA | decimal | The exponentially weighted moving average: recent values count more | EWMA("100,120,90,130,110,140", 0.3) |
SEASONAL_INDEX | decimal | How the latest value's season compares with the average: 1 is average, above 1 is a strong season | SEASONAL_INDEX("100,80,90,130,110,90,100,140", "quarter") |
SUPPLIER_HHI | decimal | The Herfindahl index of concentration on a 0 to 10,000 scale | SUPPLIER_HHI("50,30,20") |
CAPITAL_SENSITIVITY_INDEX | decimal | The fraction of enterprise value moved by a capital event | CAPITAL_SENSITIVITY_INDEX(150000, 1000000) |
Errors and exceptions
| Error | What happened | How to recover |
|---|---|---|
HTQL-5001 | The function was given the wrong number of arguments. The message names the function and the counts | Give it the arguments listed under Parameters |
HTQL-1004 Unsupported function | The name is not a function this engine runs | Check the name and its spelling against this entry |
HTQL-2001 | An argument is out of range, such as an alpha of 0, too few values, an unknown period or a negative share | Pass values inside the range listed under Limitations |
HTQL-8003 | A value in the text is not a number (#VALUE!) | Pass numbers separated by commas |
HTQL-5004 Not enough params | An argument is missing | Give every argument listed |
Limitations
SMAneeds at leastnvalues.SEASONAL_INDEXneeds one full cycle of values and an average that is not 0.EWMAneeds at least one value.- The series is one text. A list or a column is not accepted: build the text of numbers first.
SUPPLIER_HHIrefuses a negative share. Shares are read as fractions when every one is 1 or less, otherwise as percentages.SEASONAL_INDEXanswers 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 inIFand used inADD,SUBTRACT,MULTIPLYandDIVIDE.
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.