Thresholds

Target values added to charts to highlight if a series value has met the target.

ChartSQL introduces @thresholds to emphasize if a series item has exceeded (or not met) a particular target value.

-- @chart: column
-- @title: Baselines - Average Baseline
-- @subtitle: An example column chart showing sales by month
-- @formats: currency
-- @series: Sales
-- @thresholds: Sales(50000)
SELECT 
	TRUNC(date_closed, 'MONTH') as Month,
	sum(amount) as Sales
FROM sales
GROUP BY TRUNC(date_closed, 'MONTH')
ORDER BY TRUNC(date_closed, 'MONTH') ASC

An example column chart with a baseline

Thresholds Directives Overview

• @hresholds: Specifies the series to which the thresholds will be applied. By default, the target for a threshold is the average of the series. You can specify custom targets.

  • average (default):

  • a number: Specify a hard coded number for the series threshold

  • column: Specify a column from the dataset that sets the threshold for the series item

• @threshold-styles(optional):

  • success (default): Series item will be green if met

  • alert: Series item will be blue if met

  • warning: Series item will be yellow if met

  • danger: Series item will be red if met

  • color: A custom color for the series item

• @threshold-criteria(optional): By default, a threshold is activated when the series item exceeds the target value. You can also activate it when it

  • above (default): When the series item is above the threshold, then it will be activated

  • below: When the series item is below the threshold, then it will be activated

• @threshold-lines (optional):

  • auto (default): Defaults to a baseline, unless we are using thresholds from a column, and the values within the column differ then we will use an item line.

  • baseline: Will be rendered as a line across the whole series. When the target value is from a column, the first value is used to create the line.

  • item: Will be rendered as a line across the particular series item. Used when each series item has a different threshold value

  • none: Do not render the threshold lines, only the style will be activated when the threshold is met.

Examples

A column chart showcasing sales with a threshold for the target sales we would like to hit

-- @chart: column
-- @title: Baselines - Average Baseline
-- @subtitle: An example column chart showing sales by month
-- @formats: currency
-- @series: Sales
-- @thresholds: Sales
SELECT 
	TRUNC(date_closed, 'MONTH') as Month,
	sum(amount) as Sales
FROM sales
GROUP BY TRUNC(date_closed, 'MONTH')
ORDER BY TRUNC(date_closed, 'MONTH') ASC

A chart with multiple baselines with specified types:

-- @chart: column
-- @title: Baselines - Multiple Baseline Types on one Series
-- @subtitle: An example column chart showing sales by month
-- @formats: currency
-- @series: Sales
-- @baselines: Sales, Sales, Sales
-- @baseline-types: average, min, max
SELECT 
	TRUNC(date_closed, 'MONTH') as Month,
	sum(amount) as Sales
FROM sales
GROUP BY TRUNC(date_closed, 'MONTH')
ORDER BY TRUNC(date_closed, 'MONTH') ASC