Charting data

The Chart component makes it easy to quickly build interactive charts in your Retool apps, while also providing the full flexibility and customizability of the Plotly.js charting library for more advanced use cases.

📘

Still using the old chart component?

A new chart component was introduced on March 24, 2021. If your organization is not ready to start using the new component you can re-add the old chart to your components menu by using the toggle available in Settings > Beta. You can find the documentation for the old chart component here.

Getting started

Here is a quick tutorial on building a bar chart. To follow along create a REST query to fetch sample employees data from this gist.

Choosing your data

You can use a query, transformer or any JS within {{}} as the data source for a chart. You can reference an array or an object in the Data source field -- Retool automatically detects the data type and transforms for graphing. This means if your data source is an array and you toggle the Use JS option you'll see Retool's built-in formatArrayAsObject function used to wrap the data source reference.

Once you've selected a data source Retool will automatically parses the data and populate the chart. You can customize the chart by changing the values of the Chart type, X-axis, Group by properties, as well as properties on each dataset.

By default the component will use the first non-numeric column to populate the X-axis field, use the remaining non-numeric columns to populate the X-axis and Group by drop-downs and create a Dataset for each numeric column.

Supported data structures

The component expects your data to be in a tabular format -- either as an array of objects where every object field is a column or as an object where each key represents a column and the value is an array of values for that column.

[
    {"date": "2010-03-01", "animal": "dog", "count": 4},
    {"date": "2010-03-01", "animal": "cat", "count": 6},
    {"date": "2010-04-01", "animal": "frog", "count": 10},
    {"date": "2010-04-01", "animal": "cat", "count": 3},
    {"date": "2010-04-01", "animal": "dog", "count": 2}
 ]
{
    "date": ["2010-03-01","2010-03-01","2010-04-01","2010-04-01","2010-04-01"],
    "animal": ["dog","cat","frog","cat","dog"],
    "count": [4,6,10,3,2]
}

Grouping data

One common use case is to chart time series data for dynamic number of groups (say revenue by product or headcount by team). You can use the Group by property to choose a field from your data source that will be used to group and display your data.

Let's create a transformer that returns the sample data provided in the Supported data types section above, select it as the data source for our chart and group by the animal type field. You can see that a series is automatically created for each type of animal.

Now let's add 4 more data points to the transformer.

[ 
    {"date": "2010-03-01", "animal": "bird", "count": 4},
    {"date": "2010-05-01", "animal": "bird", "count": 8},
    {"date": "2010-04-01", "animal": "bird", "count": 2},
    {"date": "2010-03-01", "animal": "frog", "count": 6},
]

The chart automatically picks up and plots the new data points!

Datasets

Aggregation methods

Retool's UI supports two types of transformations: Group by and Aggregation.

Group by transforms allow you to group your data. (In some documentation Plotly refers to group by as a "split" transform).

Aggregation transformations allows you to summarize values for a group.

MethodDescription
countReturns the quantity of items for each group.
sumReturns the summation of all numeric values.
avgReturns the average of all numeric values.
medianReturns the median of all numeric values.
modeReturns the mode of all numeric values.
rmsReturns the rms of all numeric values.
stddevReturns the standard deviation of all numeric values.
minReturns the minimum numeric value for each group.
maxReturns the maximum numeric value for each group.
firstReturns the first numeric value for each group.
lastReturns the last numeric value for each group.

Source: https://plotly.com/javascript/aggregations/

There are two other transformation method available if you edit the Plotly JSON directly: Filter and Sort.

Customizing the Layout

Axis tick formatting

Use the X-axis ticket format and Y-axis tick format fields to customize tick labels using a valid d3-format specifier. For a full list of numerical values, use d3-format specifier. See d3-time-format for a full list of date-time formatters and d3-format for a full list of numerical formatters. Here are a few examples of commonly used formats:

SpecifierExampleDescription
%I:%M%p12:00 PMUses %I for 12-hour clock hour, %M for minute and %p for AM or PM
Q%q %YQ2 2020Uses %q for quarter of the year and %Y for the year with century
%Input: 0.05 Output: 5%% multiplies by 100, and then decimal notation with a percent sign.

Adding interactivity

When points are selected on a chart Retool exposes the underlying data via the .selectedPoints array property on the chart. This means you can filter, transform or update your app by referencing the chart.selectedPoints array property in any component, query, or JS function.

For example, to use the x values of selected points to filter a query you can write {{ chart.selectedPoints.map(d => d.x) }}. Here is an example in a BQ SQL query:

SELECT id, event, timestamp, value, trigger 
FROM big_query.event_table 
WHERE timestamp IN UNNEST( {{chart.selectedPoints.map(d => d.x) }})

Advanced charting

For users interested in advanced charting Retool exposes the JSON passed to the data and layout attributes. Toggle on Plotly JSON.

The Data field in Retool populates the plotly.js data attribute. Use this attribute to define a list of traces (datasets) to chart. The schema of each trace is dependent on the type.

The Layout field in Retool populates the plotly.js layout attribute. Use this attribute to control positioning and configuration of non-data-related parts of the figure include title, axes, colors and legend.

Visit the plotly.js docs to read more about the data structure that the Plotly library operates on.

Common data options:

[
  {
    "name": "id",
    "x": ["2020-01-01","2020-01-02","2020-01-03","2020-01-04","2020-01-03"],
    "y": [4,8,3,12,7], 
    "type": "bar",  
    
    "marker": { // used to customize how data points are displayed on chart
      "color": "#3c92dc",
      "size": 3,
     "symbol": "circle"
    },
    "line": {  // used to customize how data lines are drawn on chart
      "color": "#3c92dc", 
      "width": 3 
    },
    "transforms": [
      {
        "type": "aggregate",
        "groups": ["2020-01-01","2020-01-02","2020-01-03","2020-01-04","2020-01-03"],
        "aggregations": [
          {
            "target": "y",
            "func": "sum",
            "enabled": true
          }
        ]
      }
    ]
  }, ...}
]

Common layout options:

{
  "title": "My Chart Title",
  "font": {
    "family": "Inter" // Default font for all chart labels
  },
  "xaxis": {
    "title": "Date", // Text label for x-axis
    "type": "-", // x-axis type
    "tickformat": "YYYY-MM", // d3-format specifier. If empty or "" plotly will attempt to guess format.
    "automargin": true,
    "fixedrange": true
  },
  "yaxis": {
    "title": "Count",
    "tickformat": "", // d3-format specifier. If empty or "" plotly will attempt to guess format.
    "automargin": true,
    "fixedrange": true
  },
  "showlegend": true,
  "legend": {
    "xanchor": "left",
    "x": 1.05,
    "y": 0.5
  },
  "hovermode": "x unified",
  "margin": {
    "l": 75,
    "r": 25,
    "t": 80,
    "b": 75
  }
}

Adding a second y axis

If you're working on a chart that shows absolute numbers and percentages (or for any other good reason), you might want to show two (or more) different y axes on your Plotly chart in Retool. To get started, we'll assume you already have a query that pulls the data you need into your datasets in the Plotly chart component.

For example, let's say we want to graph the number of visits and visits to our marketing site on the same graph as the percentage of those visits that are from new users vs. existing users. The left y axis should map to a count, and the right one should map to a percentage (out of 100%).

You'll basically need to do two things to get your second y axis working:

1) Add the second y axis to the Plotly Layout JSON

Click on your Plotly chart component, head over to the right sidebar, and scroll down to the layout section. If you're toggled to the UI Form tab, you'll need to switch to the Plotly JSON option to configure this properly. In the existing JSON, you should see an entry for an existing y axis with a "yaxis" key. We're going to add another one called "yaxis2" and configure it to show up on the right side of our graph using side: right and overlaying: y - without them, things won't work.

  "yaxis2": {
    "title": {
      "text": "an axis title",
      "standoff": 12,
      "font": {
        "size": 12
      }
    },
    "overlaying": "y",
    "side": "right",
    "type": "linear",
    "automargin": true,
    "fixedrange": true,
    "zerolinecolor": "#DEDEDE"
  }

We can also add in some optional fields to format the axis as a percentage and restrict the range to [0,1].

"tickformat": "%",
"range": [0,1],

2) Assign the datasets to the appropriate axes in the Plotly Data JSON

Once we've defined the second y axis in the layout JSON, we need to assign whichever datasets we want to that axis. Head over to your chart's settings, and toggle the data section to Plotly JSON instead of the UI Form. For each dataset that you want to assign to the second y axis, just add this property:

"yaxis": "y2"

And that's it!

FAQs

I have timestamp data. Can I aggregate by day, week or month?

Yes! You can use moment.js to truncate your timestamps.

.map(d => moment(d).startOf('week').format())

Did this page help you?