Visualizing data
Semaphor uses the open-source ChartJS 4 specification (opens in a new tab) for its visuals. You can access the full ChartJS API to customize the look and feel of your charts. Here are some examples of how to quickly create common charts using simple SQL queries.
Bar / Line / Area Charts
You can create a bar or a line chart using a simple GROUP BY clause. The first column of the query corresponds to the x-axis, while the second column (number) corresponds to the y-axis.
SELECT sub_category, -- x-axis
COUNT(*) -- y-axis (number)
FROM sales_data
GROUP BY sub_category
ORDER BY sub_category DESC LIMIT 100
| sub_category | count |
| ------------ | ----- |
| Tables | 319 |
| Supplies | 190 |
| Storage | 846 |
| Phones | 889 |
| Paper | 1370 |
| Machines | 115 |
| Labels | 364 |
Multivariate Bar
To create a multivariate bar chart, you could add another aggregate metric, such as Avg_Sales, to the above query. You can add as many metrics you would like.
SELECT sub_category, -- x-axis
COUNT(*), -- y-axis-1
AVG(sales) as AVG_sales -- y
FROM sales_data
GROUP BY sub_category
ORDER BY sub_category DESC LIMIT 100| Sub Category | Count | Avg_Sales |
| ------------ | ----- | --------- |
| Tables | 319 | 648.80 |
| Supplies | 190 | 245.65 |
| Storage | 846 | 264.59 |
| Phones | 889 | 371.21 |
| Paper | 1370 | 57.28 |
| Machines | 115 | 1645.55 |
| Labels | 364 | 34.30 |
| Furnishings | 957 | 95.83 |
Combo Chart
You can also change any of the dataset in the chart config to a line type to create a combo chart.
"datasets": [
{
"label": "Count",
"data": [],
"backgroundColor": "#3b82f6",
"borderColor": "#3b82f6"
},
{
"type": "line",
"label": "Avg_sales",
"data": [],
"backgroundColor": "#14b8a6",
"borderColor": "#14b8a6"
}
]
Area Chart
To create an area chart, add the type: line and fill: origin properties to the chart configuration. For more detailed customization, refer to the Chartjs docs (opens in a new tab).
"datasets": [
{
"type": "line",
"fill": "origin",
"label": "Count",
"data": [],
"backgroundColor": "#3b82f6",
"borderColor": "#3b82f6"
},
{
"type": "line",
"fill": "origin",
"label": "Avg_sales",
"data": [],
"backgroundColor": "#14b8a6",
"borderColor": "#14b8a6"
}
]
Stacked Charts
You can generate a stacked line, bar, or area chart using the following query structure. Notice the column in the SELECT clause . The first column is mapped to the x-axis, the second column values form a stack, and the third column represents a metric (a number).
SELECT
TO_CHAR(DATE_TRUNC('month', order_date), 'MM-YYYY') AS month, -- x-axis
category, -- stack-by
COUNT(*) AS number_of_orders -- y-axis (number)
FROM
sales_data
GROUP BY
month, category
ORDER BY
month, category;
Stacked Bar
| Month | Category | Number of Orders |
| ------- | --------------- | ---------------- |
| 01-2020 | Furniture | 22 |
| 01-2020 | Office Supplies | 45 |
| 01-2020 | Technology | 22 |
| 01-2021 | Furniture | 24 |
| 01-2021 | Office Supplies | 100 |
| 01-2021 | Technology | 31 |
| 01-2022 | Furniture | 15 |
Stacked Line
You can convert the above chart into a stacked line chart by modifying the dataset type in the config as highlighted below.
"datasets": [
{
"type": "line",
"label": "Furniture",
"data": [],
"backgroundColor": "#3b82f6",
"borderColor": "#3b82f6"
},
{
"type": "line",
"label": "Office Supplies",
"data": [],
"backgroundColor": "#14b8a6",
"borderColor": "#14b8a6"
},
{
"type": "line",
"label": "Technology",
"data": [],
"backgroundColor": "#f59e0b",
"borderColor": "#f59e0b"
}
]
Stacked Area
You can convert the above chart into an area chart by providing the fill property as highlighted below.
"datasets": [
{
"type": "line",
"fill": "origin",
"label": "Furniture",
"data": [],
"backgroundColor": "#3b82f6",
"borderColor": "#3b82f6"
},
{
"type": "line",
"fill": "origin",
"label": "Office Supplies",
"data": [],
"backgroundColor": "#14b8a6",
"borderColor": "#14b8a6"
},
{
"type": "line",
"fill": "origin",
"label": "Technology",
"data": [],
"backgroundColor": "#f59e0b",
"borderColor": "#f59e0b"
}
]
Pie / Donut / Polar Charts
You can create a pie chart, donut chart, or polar chart using the same SQL syntax.
SELECT ship_mode, -- label
COUNT(*) -- measure
FROM sales_data
GROUP BY ship_mode ORDER BY ship_mode
DESC LIMIT 100| Ship Mode | Count |
| -------------- | ----- |
| Standard Class | 5968 |
| Second Class | 1945 |
| Same Day | 543 |
| First Class | 1538 |
Pyramid / Tornado Chart
If you want to visualize two cohors side by side for a set of categories, you can create a pyramid chart using the following query structure.
SELECT category, -- dimension field
ship_mode, -- cohort field
COUNT(*) as count -- metric
FROM sales_data
WHERE ship_mode in ('Standard Class', 'Second Class') -- condition to select two cohorts
GROUP BY category, ship_mode
ORDER BY countThe WHERE clause in the above query narrows the values of ship_mode column into two cohorts - Stadnard Class and Second Class.
| Category | Ship Mode | Count |
| --------------- | -------------- | ----- |
| Technology | Second Class | 366 |
| Furniture | Second Class | 427 |
| Technology | Standard Class | 1082 |
| Office Supplies | Second Class | 1152 |
| Furniture | Standard Class | 1248 |
| Office Supplies | Standard Class | 3638 |
Range Charts
A range chart displays the variation between two data points over a period, highlighting the difference between minimum and maximum values. Ideal for visualizing ranges such as temperature fluctuations, price intervals, salary ranges, etc.
Simple Range Chart
You can display a simple range chart using the following query structure:
SELECT
sub_category, -- y-axis (dimension)
'Profit Range' as group, -- chart label
MIN(profit) as min_profit, -- from value
MAX(profit) as max_profit -- to value
FROM sales_data
GROUP BY sub_category
ORDER BY sub_category DESCThe group column is used to display the legend.
| sub_category | group | min_profit | max_profit |
| ------------ | ------------ | ---------- | ---------- |
| Tables | Profit Range | 319 | 629.01 |
| Supplies | Profit Range | 190 | 327.51 |
| Storage | Profit Range | 846 | 792.27 |
| Phones | Profit Range | 889 | 1228.18 |
| Paper | Profit Range | 1370 | 352.3 |
| Machines | Profit Range | 115 | 2799.98 |
| Labels | Profit Range | 364 | 385.38 |
| Furnishings | Profit Range | 957 | 387.57 |
Custom Range Chart
In certain cases, you might want to include a midpoint value within the range. For example, when displaying the minimum, mean, and maximum salary for a specific job role, you can achieve this by adding a thrid number column to the query, as shown below.
SELECT
sub_category, -- y-axis (dimension)
'Profit Range' as group, -- chart label
MIN(profit) as min_profit, -- from value
MAX(profit) as max_profit, -- to value
AVG(profit) as avg_profit -- mid value
FROM sales_data
GROUP BY sub_category
ORDER BY sub_category DESC| sub_category | group | min_profit | max_profit | avg_profit |
| ------------ | ------------ | ---------- | ---------- | ---------- |
| Tables | Profit Range | 319 | 629.01 | 244 |
| Supplies | Profit Range | 190 | 327.51 | 294 |
| Storage | Profit Range | 846 | 792.27 | 325 |
| Phones | Profit Range | 889 | 1228.18 | 350 |
| Machines | Profit Range | 115 | 2799.98 | 329 |
| Bookcases | Profit Range | 228 | 1013.13 | 285 |As you can see, even though you have provided the avg_profit, the chart below currently displays only two values: min_profit and max_profit.
To include avg_profit, you'll need to customize the chart by drawing a line at that specific value. You can achieve this by adding the below code snippet to the Custom Code section of the visual.
function toolTipLabel(tooltipItem) {
const data = tooltipItem.raw; // Get the raw data for the hovered item
const minValue = data[0];
const maxValue = data[1];
const midValue = data[2];
return `Min: ${minValue}, Max: ${maxValue}, Mid: ${midValue}`;
}
function drawLine(chart) {
const ctx = chart.ctx;
const xScale = chart.scales.x;
chart.data.datasets.forEach(function (dataset, i) {
const meta = chart.getDatasetMeta(i);
meta.data.forEach(function (bar, index) {
const value = dataset.data[index];
const minValue = value[0];
const maxValue = value[1];
const midValue = value[2];
const minMaxLabel = `Min: ${minValue}, Max: ${maxValue}`;
const midLabel = `Mid: ${midValue}`;
// Calculate the x position for the middle value based on the x-axis scale
const xMid = xScale.getPixelForValue(midValue);
const barTop = bar.y - bar.height / 2;
const barBottom = bar.y + bar.height / 2;
ctx.fillStyle = 'black';
ctx.textAlign = 'center';
ctx.textBaseline = 'middle';
// Draw the line at the x-axis value
ctx.beginPath();
// ctx.setLineDash([1, 5, 6]); // Dashed Line
ctx.moveTo(xMid, barTop); // Start at the top of the bar
ctx.lineTo(xMid, barBottom); // End at the bottom of the bar
ctx.strokeStyle = getDatalabelColor({ chart });
ctx.lineWidth = 2; // Adjust line width as necessary
ctx.stroke();
});
});
}
return {
plugins: [
{
afterDatasetsDraw: drawLine,
},
],
options: {
plugins: {
tooltip: {
enabled: true,
callbacks: {
label: toolTipLabel,
},
},
},
},
};In the Explorer view, click on the Gear Icon ⚙️ to open the customize dialog, paste the above code and apply your changes.
Now, you can see the line drawn at avg_profit, along with the updated tooltip displaying all three values. You can further customize the line style (e.g., dotted, dashed) by adjusting the code.
Split Range Chart
In some use cases, you might want to further split the ranges within by group. For example, you could break down the minimum, maximum, and average profit by ship mode across different sub-categories. You can structure your query like this:
SELECT
sub_category, -- y-axis (dimension)
ship_mode, -- group
MIN(profit) as min_profit, -- from value
MAX(profit) as max_profit, -- to value
AVG(profit) as avg_profit -- mid value
FROM sales_data
where sub_category in ('Machines', 'Bookcases')
GROUP BY sub_category, ship_mode
ORDER BY sub_category, ship_mode DESCThe ship mode column is used as a grouping column for the ranges.
| sub_category | ship_mode | min_profit | max_profit | avg_profit |
| ------------ | -------------- | ---------- | ---------- | ---------- |
| Bookcases | Standard Class | 124 | 1013.13 | 180 |
| Bookcases | Second Class | 48 | 291.38 | 179 |
| Bookcases | Same Day | 7 | 271.42 | 256 |
| Bookcases | First Class | 49 | 225.74 | 194 |
| Machines | Standard Class | 68 | 2400.97 | 235 |
| Machines | Second Class | 14 | 2799.98 | 354 |
| Machines | Same Day | 10 | 2229.02 | 244 |
| Machines | First Class | 23 | 1459.2 | 130 |
Text Visual
The Text Visual is designed to display formatted text based on the results of a query. You can use it to present dynamic content directly from your data source, with the added ability to include HTML tags for custom formatting.
Start by writing a query that returns the data you wish to display. Ensure that the query’s first row contains the value you want to show.
SELECT 'I would like to display this text for ' || sub_category as text FROM table
LIMIT 1
If needed, you can include HTML tags within the value to format the text. For example, you might use <strong> for bold text, <em> for italics, or <a> to create hyperlinks.
SELECT '<strong style="color:blue;">Recommendation:</strong>
<br/> The units in the <em>' || sub_category || '</em> sub category might be getting delayed. A quick inspection could prevent any unexpected surprises.' as text
FROM table
LIMIT 1;