Relative Change Expressions

View as Markdown

Summary

  • Relative Change Expressions evaluate the type of change made to a cell's data
  • ANY_CHANGE returns a boolean; ABSOLUTE_CHANGE and PERCENT_CHANGE return the size of the change, which is compared to give a boolean rule
  • They are only available in the Flashing Cell and Alert Modules (which evaluate against a data change)

Relative Change Expressions examine the nature of changes made to a cell's data.

Note

They are a specialist form of Standard Expression — still evaluated per row, but against the change rather than the current cell value

AdapTableQL provides 3 Relative Change Expression Functions to deal with different use cases:

  • ANY_CHANGE
  • ABSOLUTE_CHANGE
  • PERCENT_CHANGE
AdapTableQL: Relative Change Expressions
Fork
  • A price feed updates Last and Volume - occasionally Last jumps by 1.5% - 3% and Volume sees a block trade
  • 2 Flashing Cells are configured:
    • Last flashes green or red only for a big move - PERCENT_CHANGE($SCOPE) > 1
    • Volume flashes on every update - ANY_CHANGE($SCOPE)
  • A Relative Change Alert fires for a block trade - ABSOLUTE_CHANGE([volume], 'INCREASE') > 50000
Try It Out
  • Watch Last - most ticks are small and do not flash; only moves of more than 1% flash
  • See the Block Trade Alert in the Status Bar (and the highlighted Volume cell)
  • Edit the Flashing Cell Rule to PERCENT_CHANGE($SCOPE, 'DECREASE') > 1 so that only falls flash
Loading demo…

Any Change

The ANY_CHANGE function checks if any change whatsoever has been made to a cell's value.

Hint

This is commonly used when setting up Flashing Cells

 // A data change of any value has been made to the Price Column
 ANY_CHANGE([PRICE])

When used with Column Scope (in an Alert or Flashing Cell), $SCOPE refers to the column that changed:

 // Same rule, using the Alert / Flashing Cell Column Scope
 ANY_CHANGE($SCOPE)

Absolute Change

The ABSOLUTE_CHANGE function is used when you want to check if the change is of a certain amount.

It most commonly takes a (numeric) Column as the first argument (or $SCOPE for the scoped / changed column).

Caution

The Column must be numeric as the evaluation only works on numeric values

 // Value in the Price Column has changed by more than 10
 ABSOLUTE_CHANGE([PRICE]) > 10
 // Same rule, using the Alert / Flashing Cell Column Scope
 ABSOLUTE_CHANGE($SCOPE) > 10

It is possible to use "INCREASE" to limit the evaluation to changes where new value goes up...

 // Value in the Price Column has increased by more than 10
 ABSOLUTE_CHANGE([PRICE], 'INCREASE') > 10

...or "DECREASE" to limit the evaluation to changes where new values goes down

 // Value in the Price Column has decreased by 5
 ABSOLUTE_CHANGE([PRICE], 'DECREASE') = 5

Percent Change

The PERCENT_CHANGE function is similar to ABSOLUTE_CHANGE, but used to evaluate the relative change in value.

It most commonly takes a (numeric) Column as the first argument (or $SCOPE for the scoped / changed column).

Caution

The Column must be numeric as the evaluation only works on numeric values

 // Value in the Price Column has changed by more than 10%
 PERCENT_CHANGE([PRICE]) > 10
 // Same rule, using the Alert / Flashing Cell Column Scope
 PERCENT_CHANGE($SCOPE) > 10

Again, you can use "INCREASE" to limit the evaluation to upward percentage changes...

 // Value in the Price Column has increased by more than 50%
 PERCENT_CHANGE([PRICE], 'INCREASE') > 50

...or "DECREASE" to limit the evaluation to downward percentage changes

 // Value in the Price Column has decreased by 10%
 PERCENT_CHANGE([PRICE], 'DECREASE') = 10

Find Out More