Charting using PlotlyJS in Retool: Second Y-axis, Changing Date Formats, Filtering Chart Data by Date

Charts are a necessary tool for visualizing data and are often required in Retool apps. While it is possible to make all sorts of charts with Retool’s UI components, Retool has now added the Plotly JSON feature, which allows you to edit the JSON that generates the data and layout attributes of the chart, offering a wider variety in both functionality and formatting.

Plotly is an open-source graphing library, and, if you are not familiar with it, it can be a bit of a learning curve. There is a lot of information available on the Plotly docs page, but it’s not always evident how to apply that information in Retool. In this tutorial we will cover three topics that are often necessary in charts but can be somewhat challenging to navigate using the Retool Plotly interface:

1. Adding a second y-axis
2. Changing the date format
3. Filtering the chart data by date

Setting Up a Basic Graph

‍Retool covers setting up basic graphs in their documentation, but here is a basic recap to illustrate how exactly the one in this tutorial is working (and give you something to work from if you are unfamiliar with the charts component).

For this tutorial I used dummy data created using the Retool API Generator, which you can learn to use in our API Generator tutorial. As an obsessive gardener, I went with Precipitation and Temperature vs. Date for my data.

For the sample data set, I chose 50 rows and made 3 new columns, which are listed here for reference:

1. Column type: Date and Range: March 1, 2021 to June 1, 2021
2. Column type: Numbers/Random, Title: Temperature and Range: 0 to 35
3. Column type: Numbers/Random, Title: Precipitation, Range: 0 to 3 with 2 decimal places

And here is how that looks in the API Generator:

Now that we have our data, we need to pull it into Retool. I added a RESTquery (mine is called getData) with action type GET, entered the Endpoint URL from the REST API Generator and ran it. Next insert a chart with the data source that you just created {{getData.data}}.

Under ‘Datasets’, you can hide ‘id’ as it’s not needed in the chart. Below you can see the default, basic bar chart that is created. You can also probably see that it’s a bit of a mess, with the date formatting difficult to read, and the range between the temperature and precipitation values too large to read accurately on the chart. In the next sections of this tutorial we’ll look at clearing these things up.

Adding a second y-axis

When comparing two sets of data, sometimes they just don’t make sense on the same axis or the value ranges differ so widely that having them on the same axis just isn’t an option visually. This is where adding a second y-axis can be helpful.

On the chart we just made, the y-axis values span from 0 to 35 in order to accommodate the whole range of both sets of data, meaning the values for ‘Precipitation’ are hard to decipher as they are so much lower than the ‘Temperature’ values. Therefore, we need to get ‘Precipitation’ set up on a second y-axis to make the data easier to read and compare.

This is where we run into the limitations of the UI Form, so we will have to switch to the Plotly JSON tab to go further. (Note that once you make changes in Plotly JSON you cannot switch back to the UI Form without having to revert those changes.)

In the chart component, the Plotly JSON can be found in the tab next to the UI Form (see below) and there you will see a Plotly JSON section for both data and layout attributes. Plotly's graph description places these attributes into two categories, in Retool these attributes appear under the chart component as the ‘Data’ and ‘Layout’ section in the right-hand panel. Head to the Plotly documentation for more information about their setup.

To add a second y-axis we’ll need to edit both the data and the layout attributes. First we’ll go to the ‘Layout’ section (below ‘Data’) of the chart component, switch to the ‘Plotly JSON’ tab and add the following:

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

I also changed the title text of the second y-axis (in this case ‘Precipitation’) and the title text for the first y-axis (in this case ‘Temperature’). See code below for where I changed those.

Below is what the chart looks like after those additions to the code.

As you can see on the left side of the chart, the y-axis title text has been added, but not the second y-axis text (which would appear on the right side). This is because, while we’ve created a second y-axis, it’s not yet been assigned to any data. To do that, we need to go to the Plotly JSON section located under Data and add “yaxis” : “y2” as a property under our second y-axis data (in my case, the Precipitation data).  

As I did, you can also change the “type” on the second axis to “line”, rather than the default “bar”, and change the color of one of the bars (I did ‘Temperature’) to distinguish them more clearly.

See below for how this looks in the code:

Now, as you can see below, the 2nd y-axis is visible on the right-hand side and the data is much easier to compare.

Now, to improve this graph even more, let’s have a look at simplifying the x-axis so that the dates are easier to read. Head to the next section for how.

Changing the date format

There’s a wee bit of code that can help change the date formatting here and fix our problems with the x-axis. In their current format, Retool isn’t treating the dates field as an actual date. We can use the moment library (an external JavaScript library which allows you to format date and time, pre-installed in Retool) to fix this. You can use this mapping function to format the data to be recognized as dates, which means Retool will interpret the data and present it a bit more logically. The mapping function simply runs a specified function on each item in an array and returns that data in the correct format.

You can do this by adding the below code to the end of all x-axis values in the Data Plotly JSON box:

.map(d=>moment(d).format(“YYYY-MM-DD”)

So this is basically saying “Take all the data points in this array, recognize them as dates, put them in order and format them as YYYY-MM-DD”.

Here, I used the format “YYYY-MM-DD”, however, there are a number of formats you could choose. This site explains them.

So, as an example, since my x-axis data value is {{(formatDataAsObject(getData.data).Date)}}, I would add the moment mapping code as above to make it:

{{(formatDataAsObject(getData.data).Date).map(d=>moment(d).format("YYYY-MM-DD"))}}

Below you can see all the places (starred) where you will need to add the mapping function.

Filtering Dates

‍Now that our x-axis is in order, another thing you might want to do is filter the chart by specific dates, which can be programmed to be manually changed by the user, or even to change automatically based on functions such as moment(). In this tutorial we will look at two manual options, where the user can define the data range using either the daterangepicker or the dropdown box components.

Filter Dates using the daterangepicker

The daterangepicker component allows the user to define the ‘from’ and ‘to’ date that will filter their data, using a simple and familiar UI. Your end result will look something like this:

To do this, insert the daterangepicker component into your app. Then, add a new Resource query (Query JSON with SQL) and name it (I went with filterData). You will use this SQL query to filter your data according to the dates that get selected in the daterangepicker.

Your SQL query should look something like this, changing the values in bold to those in your app/data source:

SELECT * FROM 
{{(formatDataAsObject(getData.data).Date).map(d=>moment(d).format("YYYY-MM-DD"))}}
WHERE Convert (datetime, Date) >= Convert(datetime, {{daterangepicker1.startFormattedString}})
AND Convert (datetime, Date) <= Convert(datetime, {{daterangepicker1.endFormattedString}})

Make sure that ‘Run query automatically when inputs change’ is selected from the dropdown above the value box.  

Now let’s head back to the daterangepicker component.

The query references the daterangepicker and fixes our data range based on what is chosen, so we can use the results of the query to fill in our Start Value and End Value fields in the daterangepicker. The Start Value would be:

{{moment.(_.first(filterData.data.Date)).format(YYYY-MM-DD)}}

Moment() is used here again to recognize the value as a date. The ‘_.first’ function returns the first element of an array (my array: filterData.data.Date) and .format() is used to format the date how you would like. The End Value uses ‘_.last’ instead of ‘_.first’ but is otherwise exactly the same.

The last step here is to connect the daterangepicker values to your chart. To do this, head back to the Plotly JSON Layout box of the chart component and add the range property to the x-axis, using the start and end values of the daterangepicker as the range. See below:

Here is the result when I pick May 1, 2020 to May 31, 2020 in the daterangepicker. Voilà!

Filter a Date using a dropdown box

For many users of data-based apps, it’s useful to have a selection of simple data ranges available for speedy data selection. A handy way to do this is by using the dropdown component in combination with the moment() function. The dropdown box allows users to easily switch between date ranges and moment() will automatically correlate those ranges to the present day.

Any date range could be added to the dropdown box (options are really endless), but here is an example where I used ‘last week’, ‘last month’ and ‘last year’ as the ranges.

Keep reading to see how to do this.

Start by adding a dropdown component with values of last week, last month and last year.

Then head back into the chart Layout Plotly JSON to change the x-axis range to:

[{{dropdown1.value == “Last week” ? moment().substract(1, “weeks”) : dropdown1.value == “Last month” ? moment().subtract(1, “months”) : moment().subtract(1, “years”)}}, {{moment()}}]

This uses moment() and then subtracts either 1 week, 1 month or 1 year from the current date depending on what has been chosen in the dropdown.  So if I choose Last month, my chart looks like this.

So with that final step we now have a chart app that is easier to read and analyze by the addition of the second y-axis, has correctly formatted dates and the ability to filter by dropdown or date picker.

‍