DATA LITERACY

Mixed Time-Series Charts Tutorial | Data Viz

Srini Kadamati

We're running a live workshop on Mixed Time-Series Charts on June 16th! You can sign up here to attend or be notified when the recording is out.

Data visualization in Superset has always been deeply rooted in time-series visualization. When the Superset core contributors started to migrate chart plugins from NVD3 to Apache ECharts, they ported the time-series visualizations first.

Superset Time-series

The mixed time-series visualization in Superset helps take these charts to the next level by enabling you to combine and situate two of these charts in a single chart container.

The mixed time-series visualization was first released in Superset 1.2 and has enjoyed a number of improvements since then.

Why Combine Time Series Charts?

Starting from first principles:

  • Why would we want this capability at all?
  • What new capabilities does overlaying two time-series charts give us?

The short answer is: to expand our visualization canvas and facilitate more comparisons. When designed effectively, charts enable us to compare large quantities of data using our eyes.

In Edward Tufte’s own words: “Visual displays, if they are to assist thinking, should show comparisons.”

Mixed time-series visualizations allow us to overlay charts on the same axes, which is useful for elevating a few specific types of comparison that would be challenging as standalone charts.

In this post, I'll showcase three powerful use cases where Mixed Time-Series charts really shine and walk through the steps to create each one.

Comparing Subsets with the Full Population

Let’s say that we’re managing a growing community and we want to understand the proportion of the community that’s regularly engaging. Specifically, we want to understand the rhythms of daily active users (DAUs) and weekly active users (WAUs) as they relate to the total number of users.

Without the Mixed Time-Series chart

Before Superset supported the Mixed Time-Series chart, we had to create multiple time-series charts and place them side-by-side to attempt to enable the viewer to compare.

Time Series Dashboard

This layout has a few key disadvantages:

  • comparing values is discouraged between DAU’s and the total members because of how incredibly cumbersome it is
  • the y-axis ranges default to the ranges specific to each column, so we need to do some extra customization work in Explore to force the ranges to match

With the Mixed Time-Series Chart

Using the mixed time-series chart in Superset, we can combine both of the charts and plot them within the same charting area:

  • Line chart for current number of members in a community
  • Bar chart for daily active members in a community

Mixed Time Series

This combined chart has a few key advantages:

  • comparing daily values with the total number of members is encouraged because of the shared axis and charting
  • the y-axis scale factors in the ranges of both charts and has expanded to accommodate both data ranges
  • the shared chart container uses less horizontal space, enabling us to expand the date range from 1 months to 3 months of data without any loss of comparison fidelity

Comparing Counts with Rolling Mean

Combining the DAU bar chart with the Total Member line chart helps the viewer understand the proportion of users that are engaged daily. The next question a viewer may have is, how are DAU’s actually trending?

This is a bit difficult to answer from just the daily bar chart because there’s tremendous variation. Typically, the next step would be to apply a rolling mean to stabilize the data into a smooth line chart.

Rolling Mean Superset

Mix of Line Chart and Bar Chart

At the same time, there’s tremendous value to keeping the daily context so we can understand early (earlier than the rolling mean period!) if an intervention we made was potentially having an impact. In addition, it’s always interesting to understand the weekly rhythms (dips in weekends, spikes in weekdays) and how those may change over time.

We can combine the best of both worlds by creating a mixed time-series chart with:

  • a line chart visualizing 7-day rolling mean of daily active users
  • a bar chart visualizing daily active users

Here’s this chart in action, using the same dataset and time interval.

Rolling Mean Superset

However, out of the box the bars dominate the line. We can highlight the line more by enabling the area chart customization for that plot and tweaking the opacity.

Alternatively, you can switch the color scheme so the line is more pronounced.

Superset Theme

Mix of Line Chart and Scatter Plot

If we wanted to take things one step further and highlight the 7-day rolling mean over the daily active user counts, one option is to replace the DAU bar plot with a scatter plot instead.

Scatter Plot

This preserves our ability to look up specific DAU values for specific days while trading off our ability to easily compare between days (or groups of nearby days even).

Scatter

Comparing Rolling Windows

Currently, mixed time-series charts in Superset let you combine two charts as long as they share the same time range and time grain. However, you can change the rolling window applied to each chart.

This unlocks the ability to plot two rolling sum plots within the same chart container. In the following chart, we plot the 5-Day and 7-Day rolling mean as a mixed time-series chart:

7

By plotting both the 5-Day and 7-Day rolling means, we preserve the weekend dips that the daily bar chart was showcasing.

Hiding a Chart

Some viewers may be overwhelmed by this chart, because comparing 5-Day and 7-Day rolling means requires either previous exposure to this type of chart or some deeper introspection and exploration to really internalize it.

If you enabled the legend in a mixed time-series plot (or really in most plots in Superset), then a viewer can hide a plot just by clicking it’s name in the legend.

Deselect

How Mixed Time-Series Charts Work in Superset

Querying

At it's core, the mixed time-series chart in Superset works by generating and running two separate SQL queries and using a shared datetime range and grain to merge the data for visualization.

For example, here are the two queries generated for the chart covered in the next section in this blog post:

Mixed Time Superset

To choose the metrics, filters, etc for each query, you expand Query A and Query B.

Time

Currently, mixed time-series charts in Superset must share the same time range and grain. In the screenshot above of Explore, you'll notice that the Time section sits at the top before the Query A and Query B sections to indicate that these settings are shared across both queries.

Tutorial: Simple Mixed Time-Series Chart (Bar and Line)

Now I'll walkthrough how to create the first chart that combines a bar chart and a line chart so you can craft something similar with your own data.

  1. Create a new chart by clicking the Charts tab, then the +Chart button, choose your dataset, and then search for and select the Mixed Time-Series Chart in the visualization picker.

  2. Choose a Time Range (ideally 3-4 months) and set the Time Grain to Day.

  3. Expand the Query A section and drop in the column for Total Members or equivalent column in your dataset. Set the Aggregate to MAX if your data is pre-aggregated or choose the relevant function to aggregate otherwise.

  4. Expand the Query B section and drop in the column for Daily Active Users or equivalent column in your dataset. Set the Aggregate to MAX if your data is pre-aggregated or choose the relevant function to aggregate otherwise.

  5. Click the Customize tab. First, under Query A, set the Series Type to Line. Then, under Query B, set the Series Type to Bar.

  6. Click Update Chart at the bottom to watch your chart get created!

Timeseries

Tutorial: Mixed Time-Series Chart with Rolling Mean

  1. Create a new chart by clicking the Charts tab, then the +Chart button, choose your dataset, and then search for and select the Mixed Time-Series Chart in the visualization picker.

  2. Choose a Time Range (ideally 3-4 months) and set the Time Grain to Day.

  3. Expand the Query A section and drop in the column for Daily Active Users or equivalent column in your dataset. Set the Aggregate to MAX if your data is pre-aggregated or choose the relevant function to aggregate otherwise.

  4. Expand the Advanced Analytics Query A section and set:

  • Rolling Function to mean. This means that Superset will compute the average for the last n periods at the time grain you set earlier.
  • Periods to 7. If we set time grain to Day, rolling function to mean, and periods to 7, then Superset will compute the 7-Day rolling mean.
  • Min Periods to 0 or 7. This describes the minimum number of periods needed to show computed values. If 0, then even a 7-Day Rolling Mean will just use the first day to calculate and visualize the mean. If 7, then a 7-Day Rolling Mean will only be shown after 7 days of observed data.
  1. Expand the Query B section and drop in the column for Daily Active Users or equivalent column in your dataset. Set the Aggregate to MAX if your data is pre-aggregated or choose the relevant function to aggregate otherwise. Don't configure advanced analytics for this query

  2. Click the Customize tab. First, under Query A, set the Series Type to Line. Then, under Query B, set the Series Type to Bar.

  3. Click Update Chart at the bottom to watch your chart get created!

rolling mean

To swap out the bar plot portion with a scatter plot, change the Series Type for Query B (raw DAU values) to scatter:

rolling scatter

Tutorial: Mixed Chart with Multiple Rolling Windows

  1. Create a new chart by clicking the Charts tab, then the +Chart button, choose your dataset, and then search for and select the Mixed Time-Series Chart in the visualization picker.

  2. Choose a Time Range (ideally 3-4 months) and set the Time Grain to Day.

  3. Expand the Query A section and drop in the column for Daily Active Users or equivalent column in your dataset. Set the Aggregate to MAX if your data is pre-aggregated or choose the relevant function to aggregate otherwise.

  4. Expand the Advanced Analytics Query A section and set:

  • Rolling Function to mean. This means that Superset will compute the average for the last n periods at the time grain you set earlier.
  • Periods to 7. If we set time grain to Day, rolling function to mean, and periods to 7, then Superset will compute the 7-Day rolling mean.
  • Min Periods to 0 or 7. This describes the minimum number of periods needed to show computed values. If 0, then even a 7-Day Rolling Mean will just use the first day to calculate and visualize the mean. If 7, then a 7-Day Rolling Mean will only be shown after 7 days of observed data.
  1. Expand the Query B section and drop in the column for Daily Active Users or equivalent column in your dataset. Set the Aggregate to MAX if your data is pre-aggregated or choose the relevant function to aggregate otherwise.

  2. Expand the Advanced Analytics Query B section and set:

  • Rolling Function to mean. This means that Superset will compute the average for the last n periods at the time grain you set earlier.
  • Periods to 5. If we set time grain to Day, rolling function to mean, and periods to 5, then Superset will compute the 5-Day rolling mean.
  • Min Periods to 0 or 5.
  1. Click the Customize tab.
  • First, under Query A, set the Series Type to Line.
  • Then, under Query B, set the Series Type to Line. (so both line plots)
  1. Click Update Chart at the bottom to watch your chart get created!

dual rolling means

If you found this post useful, we're running a live workshop on Mixed Time-Series Charts on June 16th! You can sign up here to attend or be notified when the recording is out.

Subscribe to our blog updates

Receive a weekly digest of new blog posts

Close