Charts

One of the best ways to analyze and understand your data is to visualize it. Charts can often reveal outliers and interesting facts that are otherwise hidden.

Our chart documentation will walk you through the chart creator, and explain the chart types as well as various features and settings.

Chart Editor

Quick Start

To quickly get started building charts, try our Chart Editor tour. The tour is accessible via Help > Setup Guide in the top navigation of your Chartio account.

For an overview of building a basic chart, see our Quick Start Guide.

Explore

Have a quick query you want to try out, but don't necessarily want to save it to a dashboard? Click the Explore button in the top navigation to open a Chart Editor window that is not attached to any dashboard. If you decide you want to save your chart to a dashboard, you'll have the option to do so via the Save menu.

The Chart Editor has three main sections:

  1. The Layers Interface is where you build your queries. Add a layer for each separate query you want to include in your chart.
  2. The Data Pipeline allows you to join layers and apply transformations to your data results.
  3. The Chart Preview contains chart settings and other visualization options.

Layers Interface

The Layers Interface is where you build your queries. Add a layer for each separate query you want to include in your chart. If you'd like to join data from multiple data sources, you can do so by querying each source in a separate layer.

Preview Table Data

Not sure which table you need to use? Click the table icon next to a table name to quickly preview its first 100 rows.

For compatible data sources; excludes Google Analytics.

Building a query to chart total payments per month.

Measures and Dimensions

Chartio automatically sorts your columns into two groups: Measures and Dimensions. Generally, you'll want to drag Measures columns to the Measures field, and likewise for Dimensions. To use a Measure as a Dimension, see our documentation on Bucketing Measures.

Measures

Typically refer to quantitative data, such as number of units sold, number of unique visits, profit and so on. In the context of data visualization, measures map to the Y axis of a chart.

Drag a Measure column to the Measures field and click the column to select an aggregation.

Dimensions

Refer to categorical data, such as state, gender, product name or units of time (e.g., day, week, month). Generally, dimensions are used to group quantitative data into useful categories (e.g., number of units sold by state) and typically map to the X axis.

For information about date formatting, see our Knowledge Base article.

Filters

Dragging a column to the Filters field in the Chart Creator generates a list of filtering customization options. These options vary depending on whether the column is a Measure, Dimension (Non-Date), or Dimension (Date).

OR filters

By default, filters in Interactive Mode each have an AND between them. This means that the each row included in your result set must match every filter condition.

However, there are certain situations where your query results only need to match some of the filter conditions. In those cases, you can use an OR filter.

Once there's at least one filter in the Filters section, an OR button will appear below the Filter box. Click the OR button and drag your column to the new OR field.

Every condition in the same filter field will have an AND between it. You may add as many new OR filter fields as needed.

View examples

Date filters

Chartio's Interactive Mode date filters don't necessarily follow SQL behavior. We have created standard date filters to ensure date filtering behavior is consistent for both dates and datetimes across all of our supported data sources.

  • between is exclusive, which means the end date is not included
  • between and including is inclusive, which means the end date is included
2016-03-14_13-45-30.png
Relative date filters

To apply custom date filters, you can use Chartio's relative date variables. For more information, see our documentation.

 
Edit Variable Values

Use the Edit Variable Values feature to customize the dashboard variable values that you're using in chart filters or in the Data Pipeline. This allows you to preview your chart's data with non-default variable values.

Note: the edited variable values are only applied to the current Chart Editor session. Values revert to defaults when navigating away from the Chart Editor.

 

Interactive vs. SQL Mode

Building queries in Interactive Mode and then switching to SQL Mode is a great way to generate the basic structure of return values and even joins, before editing the query with more specific needs.

Interactive Mode

Any columns dropped into the pane while in Interactive Mode automatically generates an underlying SQL statement. To view the generated SQL, click the Preview SQL button.

SQL Mode

SQL mode allows you to write custom SQL against your database. SQL Mode can be useful for complex queries that aren't feasible in Interactive Mode due to complex joins, subqueries, etc.

Autocomplete

Autocomplete column and table names using the keyboard shortcut Control+Space.

Query History

Chartio saves the last 100 saved queries in each SQL Mode layer. To access, click the History button in the top bar of your SQL Mode layer.

Preview SQL

To view the SQL being generated for your Interactive Mode query, click the Preview SQL button. The SQL is updated in realtime as changes are made to your Interactive Mode query.

This is also a great way to learn some basic SQL—simply edit your Interactive Mode chart and watch how the generated query changes.

Multiple layers

See our documentation on Merging Layers.

Data Pipeline

Chartio's Data Pipeline allows you to perform transformations on your query results in a series of steps before charting it. These steps include a variety of operations such as column sorting, pivoting data, and adding calculated columns. The flexibility of adding any steps in any order allows you to get your data exactly how you want it.

Merging Data (Joins)

When adding two or more layers in the Chart Creator, you will need to select a method for merging, or blending, the layers. Merging layers allows for powerful post processing and calculations using Chartio's drag and drop interface.

For every join option except Union and Cross Join, you can choose how many columns you would like to join the layers on. View an example here.

Outer Join

Combines the columns from all layers on one or more common dimension when possible, and includes all data from both layers.

Inner Join

Combines the columns on a common dimension (the first N columns) when possible, and only includes data for the columns that share the same values in the common N column(s).

Left Join

Combines the columns on a common dimension (the first N columns) when possible, returning all rows from the first layer with the matching rows in the second layer. The result is NULL in the second layer when there is no match.

Union

A Union merge will stack the layer results on top of each other without grouping or combining the data. Unions can be used to generate lists of data to be printed or viewed in table format. To remove duplicate rows, check the Distinct checkbox.

cross-join.png
Cross Join

The result of the Cross Join will be a table with all possible combinations of your layers together. This can result in enormous tables and should be used with caution. Cross Joins will likely only be used when your layers are returning single values.

Adding a Step

Click the plus button in the pipeline wherever you want to apply a new transformation step to your query results. You can add as many steps as you'd like, in any order. You can also use each step multiple times as needed.

Preview Data

To preview your to view your data at any point in the pipeline, click the View Data link in a pipeline step. It can be incredibly useful to compare how your data looks before and after a transformation step is applied.

Data Pipeline Steps

For descriptions and examples of the available Data Pipeline steps, check out our Data Pipeline documentation.

Chart Preview

The Chart Preview section is where you select your chart type and other Chart Settings.

Chart Settings

Click the Chart Settings button above the Chart Preview to view available settings. Each Chart Type offers different customization options.

Popular chart features:

Chart Types

One of the best ways to analyze and understand your data is to visualize it. Charts can often reveal outliers and interesting facts that are otherwise hidden.

By default, Chartio auto-selects the best-fitting chart type for your data. To disable this behavior, simply select another chart type.

To see the data format required for each chart type, click its chart type icon.

Area

An Area Chart can add depth to your line chart, especially multidimensional data. You can also select a Percent Area Chart, which can help magnify relative differences.

Table formats accepted:
Two or more columns. The second through the last column must be numeric. If your chart has two dimensions and one measure, add a Pivot Data step in the Data Pipeline.

Column 1Column 2
2015-0134535
2015-0245649
......

Bar

Bar Charts are mainly used to visualize discontinuous (or discrete) data or to show the relationship between a part to a whole. For multidimensional data, you can choose a Stacked Bar Chart, Grouped Bar Chart, or Percent Bar Chart.

To show value labels on bars, open the Chart Settings and check the Value labels checkbox.

Plotting dates on a Bar Chart

Unlike charts meant to show continous data, such as Line and Scatter plot charts, Bar Charts don't sort dates or fill in missing date values. This is because Bar Charts are generally used to display discrete data points. When using a Bar Chart, sort and/or zero-fill your dates in the Data Pipeline as needed.

Table formats accepted: Two or more columns. The second through the last column must be numeric. If your chart has two dimensions and one measure, add a Pivot Data step in the Data Pipeline.

Column 1Column 2
2015-0134535
2015-0245649
......

Bar Line

Bar Line Charts use a bar and a line to visualize a data set with both a continuous and a categorical metric. They can be especially handy for comparing values against a goal line, or comparing a group of values against an average.

Bar Line charts have a dual Y-axis by default. You can switch to a single Y-axis from within the Chart Settings.

The first column in your query results maps to the x-axis, and the last column maps to the line. Any columns in between will map to one or more bars. Change the value for Last X columns as lines in your Chart Settings as needed.

Table formats accepted:
Three or more columns. The second through the last column must be numeric. If your chart has two dimensions and one measure, add a Pivot Data step in the Data Pipeline.

Column 1Column 2Column 3
2015-01345353.25
2015-02456492.67
.........

Box Plot

A Box Plot is useful for visualizing the distribution of data based on the following five groupings: lower quartile, upper quartile, minimum, maximum, and median. It is similar to a histogram, but is usually better for showing several simultaneous comparisons-such as data grouped by month, etc.

Table formats accepted:
One or two columns. The second column must be numeric.

Column 1Column 2
2015-0134535
2015-0145649
......

Bubble Map

Bubble maps are similar to Bubble Plots and Scatterplots, but accept latitude and longitude values. You can choose between the following maps: US, Europe, Africa, Australia/New Zealand, and World.

Table format accepted:
3-5 columns in the following order: label, latitude, longitude, value (optional), and group (optional). Similar to Bubble Plots, a fourth column will become the bubble's label, and a fifth column will be a category (and group the bubbles with separate colors.)

Column 1Column 2Column 3
2015-01-39.8298983174.289801
2015-01-39.0012823174.643919
.........

Bubble Plot

Bubble plots are similar to Scatterplot charts, but support three value series instead of two. The first column maps to the x-axis, the second column to the y-axis, and the third column becomes the area of the bubble.

Add an optional fourth and/or fifth column: a fourth column will become the bubble's label, and a fifth column will be a category (and group the bubbles with separate colors.)

Table format accepted:
Between 3 and 5 columns. Columns 2 and 3 must be numeric.
If 5 columns, first column must be a date or a number.

Column 1Column 2Column 3
16-5.6134
23-6.3186
.........

Bullet

Bullet graphs are ideal for displaying single values within some quantitative context, such as a goal value. You can define a maximum of 3 quantitative ranges in your chart settings.

Table format accepted:
One column with one row. Value must be numeric.

Column 1
16

Funnel

Funnel charts are often used to visualize optimizations, specifically to see which stages most affect dropoff. Visualizing the dropoffs helps to show the severity and importance of each stage.

Table format accepted:
Two columns. The second column must be numeric.

Column 1Column 2
Trial34535
Subscribe45649
......

Heat Map

Heat Maps display quantitative data as variations in color. Representing values as colors provides slightly less precision, but can allow you to display more data in a smaller area.

To sort the axis values, navigate to the Axis tab of the Chart Settings.

Table formats accepted:
Three columns total. Last column must be numeric.

Column 1Column 2Column 3
San FranciscoQ120256
OaklandQ125032
.........

Line

The Line Chart is particularly powerful for conveying changes over time. Generally, line graphs should be used to connect data along an interval scale (a continuous range of quantitative values that are divided into equal intervals, e.g., time.).

Table formats accepted:
Two or more columns. Second through last column must be numeric. If your chart has two dimensions and one measure, add a Pivot Data step in the Data Pipeline.

Column 1Column 2Column 3
2015-01-5.6134
2015-02-6.3186
.........

Map

Map Charts are great for visualizing location data. When you choose the Map Chart, Chartio automatically guesses which map you want. You can manually adjust this setting if needed.

Location input types accepted:

Maps available:

  • World map by country
  • US map by state
  • Europe by country
  • Africa by country
  • Australia by state & New Zealand (state abbreviations only)

Table format accepted:
Two columns. The first column should be a location in a recognized format, and the second column must be numeric.

Column 1Column 2
Alabama34535
Alaska45649
......

Pie

Pie Charts can be effective in showing the contributions of data segments as a percentage of a whole.

To display your Pie Chart as a Donut Chart, check the Donut chart checkbox in the Chart Settings. Show the total in the center of a Donut chart by checking the Show total in center checkbox.

Table format accepted:
Two columns. The second column must be numeric.

Column 1Column 2
Alabama34535
Alaska45649
......

Scatter Plot

Scatterplots are typically employed to find the relationship between two variables, often quantities.

Check the Linear regression checkbox in the Chart Settings to add a line of best fit to your scatter plot.

Table format accepted:
Two columns. The second column must be numeric.

Column 1Column 2
2015-0134535
2015-0145649
......

Single Value

The Single Value Chart simply displays a single value of any data type. Single Value charts only accept one measure as an input.

Table format accepted:
One column with one row.

Trend lines

If your dashboard has Snapshots enabled, you can view a line chart of historical data for your Single Value charts containing numeric values. From the chart's menu, click View Snapshot Data.

Column 1
16

Single Value Indicator

The Single Value Indicator Chart is similar to the Single Value Chart, except that it allows you to compare your Single Value against another value. It adds an up or down arrow next to the single value and shows the percent change, based on the comparison value.

To change arrow colors, go to Chart Settings > color > use custom colors.

Table format accepted:
The first column is displayed on your chart, and the second column is the value being compared against.
Note: if you need to put your values in separate layers, use Cross Join as the merge type.

Column 1Column 2
1625

Table

The default presentation mode in Chartio is the table. Tables show the data coming back from the queries to your data source in a mostly raw format. They can accept an unlimited number of measures and dimensions.

Table chart features

  • Urls are automatically formatted as hyperlinks
  • Format column values (text color and style) based on custom filters
  • To hide a table chart's column(s) on the dashboard but use the hidden column(s) in drilldowns and chart exports, hide the column in the Chart Settings
  • Show a totals row and set custom aggregations for each column

Table format accepted:
No formatting restrictions

Manage Charts

Download a chart

You can download any chart in the following formats:

  • CSV
  • PDF
  • Image (.png)
  • SVG

Hover over the chart you want to export and click the menu icon. Select Download from dropdown menu, and choose your export format. Your download will start automatically.

Clone a chart

Hover over the chart you want to clone and click the menu icon. Select Clone Chart, and choose the destination dashboard for the clone. The current dashboard will be selected by default.

Cloning a chart will make a copy of the chart and all of its layers. Once cloned, your chart is completely separate from the original, so changing the original will not affect the new chart and vice versa.

Move a chart

Hover over the chart you want to move and click the menu icon. Select Move Chart, and choose the destination dashboard for the chart.

You'll be redirected to that dashboard with the chart selected, where you can arrange your chart as desired. When positioned, click once to place your chart.

Note: if you want to copy your chart to another dashboard instead of moving it, choose Clone Chart.

Bulk editing

To edit multiple charts at once, select multiple charts either by clicking and dragging over them, or by Cmd+clicking each one.

Click the Edit Selected button, and choose the edit option that you want to apply to the selected charts.