Results tables can be useful for finding specific numbers or checking out your data, but it's usually easier to see trends and make sense of your data using charts.
In Lightdash, the data in your results tables can be visualized in a bunch of different ways:
The visualization type that you pick determines how Lightdash shows the data series in your chart. To change how your data is displayed, go into the
charts tab in the Explore view. You have the option to change the chart type shown by selecting a style from the drop-down:
You can also adjust all of the configuration settings for your chart type by clicking on the
Once you've finished creating your chart, you can share it using the URL, save the chart, download it as an image, or save it to a dashboard.
Chart types and options
Each chart type has its own configuration options. Click the
configure button next to the chart type in the
chart tab to see your options.
The Big value option is for displaying a single value, well, big.
The Big value works for any type of value: string, numeric, boolean, you name it! We always display the first value from the field in your results table.
The options for Big value include:
- Selecting which field you want to use for your Big value.
- Updating the label below the Big value value.
- Adding a comparison to the previous row
The Table option is good for looking at (surprise, surprise) tabular data, or for lists of things like user IDs or transactions.
The options for Tables include:
- Renaming the columns in your table.
- Showing/hiding the columns in your table.
- Showing/hiding the table name from the column labels.
- Showing/hiding the totals for your columns.
- Pivoting by a column.
- Transposing your table (a.k.a. pivoting your metrics)
- Locking a column from scrolling in your table.
- Adding conditional formatting to your cells.
By default, we attach the table name to your field name (just in case you've got any duplicate fields from joined tables). But, you can easily turn this off in your table viz with a toggle.
Columns, Rows, and Metrics
Table visualizations have three components:
Rows: When a field is chosen for the row area, all of the unique values for that field are populated as values in the rows of your table.
Columns: When a field is chosen for the column area, all of the unique values for that field are populated as values in the columns of your table.
Metrics: If you have metrics in your table, then each metric cell shows the summarized information for a given row + column combination.
Pivot tables (dimensions as columns)
Pivot tables help you more easily summarize larger sets of data in table visualizations. They're also helpful to identify trends between two dimensions in your data using a table visualization.
To add a pivot in your table, move a dimension to the
column section of your table configuration. This will change the dimension from having its values populate the rows values of your table, to having it populate the column values of your table.
You can move up to 3 dimensions as columns and there is a limit of 60 column values in your table (e.g. pivoting by a dimension with 30 months, and another with 4 statuses won't work because that will generate 4 x 30 = 120 column values).
Transposing your table (metrics as rows)
You can transpose your table so that your metrics become rows in your table instead of columns. This can be helpful if you want to compare a lot of metrics to each other (e.g. how my metrics are changing over time).
To move your metrics to rows in your table, you need to have at least 1 dimension as a column (i.e. at least one pivot) then, you can click on
show metrics as rows in the table configuration.
You can add column totals or row totals (in pivot tables) to your tables by selecting
show column totals or
show row totals in the chart configuration panel.
Totals are not calculated for table calculations. Also, in pivot tables, totals are not shown for
median metric types.
If your total values are different to what you'd expect, check out our FAQs page on "Why don't my totals match what's in my table?"
Locking columns from scroll
If you have a very wide table, you may want some columns to always be locked to the left while you're scrolling. In Lightdash, you can just click on the lock icon beside the columns you want to keep pinned to the left of your table visualization. Now, if someone is looking at your table, they'll always see those columns, locked in view.
Sometimes it’s helpful to highlight certain values in your tables when they meet a specific condition. You can set up conditional formatting rules by going to the Configure tab then clicking on Conditional Formatting.
When you add a new rule, you’ll first need to pick which column should be affected, the type of rule you'd like to use (
single color or
color range), then you'll need to choose condition you want to match and the colour to change the cell if it matches that condition.
You can set as many rules on a table as you want. If two or more rules disagree with each other, the rule that’s on the bottom of your list of rules will win.
To use color ranges for your rules, select
color range as the type. The color range option highlights cells with color shades according to the value in a particular cell.
By defult, the color range will use a set of 5 colors mapped across the min and max colors selected in your rule.
Pie charts can be useful visualize data that adds up to a meaningful "whole". For example, the split of total revenue across each of our product lines.
To use a pie chart type, you need at least one metric and one dimension in your query results.
To use a pie chart type, you need at least one metric and one dimension in your query results. You can add multiple dimensions to create more granular
groups in your pie charts.
By default, pie charts are shown in a donut shape (i.e. with the center removed) but you can easily toggle the
donut toggle to switch between a traditional pie and donut chart.
You can adjust the labels and colours for each of the slices of your pie chart in the
series tab. You also have the option to show or hide value labels in your chart.
You can choose to show or hide the legend for your pie chart in the
Bar charts are helpful to:
- compare things between different groups (e.g. the number of orders you have by product type)
- track how a number changes over time if you have a smaller number of x-axis values (e.g. number of new users per month over a year).
Stacked bar chart
You can also stack bar charts to compare proportions across different groups.
Stacked bar charts work best when:
- the focus of the chart is to compare the totals and one part of the totals. It's hard to compare bars if they don't start at the same baseline. So, if you're trying to build a chart to compare multiple parts of your total with each other, consider keeping your bar chart unstacked!
- you're trying to show the parts of multiple totals. If you only want to show parts of one total, consider an unstacked bar chart instead.If you only want to communicate one part of one total, consider if you should be using a big value chart instead.
You can see more details about bar chart configurations here.
Area charts are best if:
- you want to show how values develop over time. If you want to show how values differ in different categories, consider a (stacked) bar, or horizontal bar chart instead.
- the total is as important as its parts. If the total (= the height of all your stacked areas) is not important, consider a line chart instead. Many readers will have an easier time understanding a line chart than an area chart.
- there are big differences between your values. If the differences between your values are very small, consider a line chart instead. Compared to an area chart, the y-axis of a line chart doesn’t need to start at zero. This means that your y-axis can be stretched to show the tiny differences.
- you're showing multiple series over time. If you just want to show one value over time, also consider a line chart instead; especially if you don’t want your y-axis to start at zero. If you only have a few dates, you can also consider using a column chart. In both cases, labelling will be better.
- you have many data points. If you have less than ten or so data points, consider a stacked bar chart instead.
Line charts are used to show changes in a number over a short or long period of time.
They're a good option when:
- you have small changes between your values. The y-axis of a line chart doesn’t need to start at zero. This means that your y-axis can be stretched to show the tiny differences.
- you have lots of x-axis values. In this case, line charts are better to use than bar charts.
Line charts with multiple lines can also be used to compare changes over the same period of time for more than one group.
You can see more details about line chart configurations here.
Horizontal bar chart
Horizontal bar charts are just bar charts, except the columns are placed on the chart horizontally instead of vertically.
Horizontal bar charts are useful when:
- you're trying to group a number by something with a lot of possible values.
- your groups have really long label names.
You can see more details about horizontal bar chart configurations here.
A scatter chart is useful if you want to to look at the relationship, a.k.a. correlation, between two variables. Something like the age of your users vs. the amount of time they've spent on your website.
You can group your scatter chart using a third variable. This will make the points on the scatter the same colour if they have the same group value.
You can see more details about scatter chart configurations here.
You can combine bars, line, and scatter charts on the same chart using a Mixed chart.
To use a Mixed chart, you'll need to start with either a line, scatter or horizontal bar chart type and have two or more series on your chart. Either from having two or more fields selected for your y-axis or from having a group with two or more groups.
Once you have the series you want on your chart, you can pick and choose the different chart types you'd like for each series in the
series tab of the
You can easily revert all of the series on your chart to a single type using the
chart type toggle list in the
You can see more details about mixed chart configurations here.
Configure your bar, line and scatter charts
These chart types have very similar configuration options, so we thought it would be easiest to talk about them all together:
This is where you can pick the columns from your results table that you want to plot on your x and y axes or that you want to group by.
You can group by up to 3 fields in your chart.
For bar charts, this is also where you have the option to stack your bars, or leave them unstacked (grouped).
The series tab is where you can adjust how your chart shows each data series. A data series is a set of related data points plotted on a chart. For example, the number of new users created each day over a set of dates is a series. In a bar chart, a series is represented by bars of the same color; in a line chart, a series is represented by a single line. You can see a list of the series for your chart in the
series menu, and on the chart legend.
The options available in here will depend on the data in your chart. But, in here you can:
- Set the chart type of your series.
- Put your series on a left or right y-axis.
- Show the value labels on data points.
- Show or hide a series from your chart.
If you have multiple series in your chart, you can adjust these settings for all of the series in your chart at once using the configuration options at the top. Or, you can adjust these setting for each of the series individually.
Here’s where you can customize the axes on your plot.
You have the options to:
- change the text for your axes labels.
- set the axis limits for your y-axes. You can either leave the auto-axis range or manually enter your own limits.
This tab is where you're able to control the legend and reference lines in your chart.
You can add reference lines to your charts to easily visualize goals or thresholds.
To add a reference line, you just need to click
+ Add under the reference lines option, then configure your reference line in the drop-down.
- select a field you want to apply your reference line to
- select the value you want to set your line at
- customize the label on your reference line
- change the colour of your reference line
If you select a field plotted on your x-axis, then your line will be vertical. If you select a field plotted on your y-axis, then your line will be horizontal.
Note: if your reference line label is cropped off your chart, try adjusting your right margin.
You can add and adjust the legend in your chart to help people understand what data's been plotted.
- Show/hide the legend
- Adjust the position of the legend on your chart
- The values in
positionare the coordinates of the legend on your chart. They can either be numbers or percent. We suggest using %.
- If you want the position to be in the bottom right, you would set: Right = 0%, Bottom = 0%. For the legend to be in the middle of the chart, you'd set: Top = 50%, Left = 50%.
- The values in
- Set your legend to scroll (if you have so many groups that they overlap your chart).
- Orientate the values in your legened to form a list vertically, or horizontally
The Margin tab is where you're able to add or remove margin around the grid (aka your chart with its plotted values). Removing margin means that your chart will fill more of the space in the chart tab. Adding margin will shrink your chart into a smaller space.
You can either add numbers (e.g. 50) or percent values (e.g. 50%) to the margin settings listed. The default margin is set to
top = 70,
bottom = 30,
left = 5%,
right = 5%