Webinar: Why logical layers matter, and how to use them -Watch now

It's Time to Slice and Dice! Introducing Pivot Charts

Image of author
Emily Ritter, VP of Marketing

March 31, 2015

3 minute read

When we launched Mode's new SQL workflow earlier this year, we said there would be a lot to come on the charting front. Today the first set of new charting features hits the editor to help you find and deliver answers even faster.

Pivot Charts can help you explore data visually, earlier in your analytical process, and with simpler queries. Rather than exporting a large data set to Excel for pivoting and charting, you can now aggregate data right in Mode.

Let's say we want to start exploring how app logins vary by device and country. In the past, this was a pretty difficult chart to make in Mode. It not only involved lots of tedious CASE statements, but also required you to know all of the values ahead of time.

Now, you can skip the hassle and write a simple query (this example uses fake data from a SQL School case study):

SELECT DATE_TRUNC ('day', ye.occurred_at) AS day
     , ye.location
     , ye.device
     , COUNT(yu.user_id) AS users
  FROM tutorial.yammer_events ye
  JOIN tutorial.yammer_users yu
    ON yu.user_id = ye.user_id
 WHERE yu.state = 'active'
   AND ye.occurred_at > '2014-05-01'
   AND ye.occurred_at < '2014-05-08'
   AND ye.event_name = 'login'
 GROUP BY 1,2,3

When you open the chart tool, the new “Chart Data” section allows you to quickly aggregate your data. In this example, Mode automatically aggregates and displays the values for each date.

New Chart Tool

Use the “Aggregate” drop-down menu to perform calculations (Count, Average, Sum, etc). And since the dataset includes information about countries and devices, you can quickly display each country as its own series using the “Group By” drop-down menu.

Group Data by Country

In the past, looking at the same data by device would require yet another long, difficult SQL query. Now, it takes just one click to change the “Group By” selection:

Group Data by Device

After aggregating the data as needed, click “Insert Into Design” and share the report.

To play around with Pivot Charts using this data set, simply open the report in a new tab and clone it!

Turning Pivot Charts into Data Tools

Last week we discussed how parameters can help analysts turn commonly requested queries into data tools. Keep parameters in mind as you start to explore Pivot Charts. You can combine these two features to help other folks explore data on their own.

Instead of writing the SELECT statement with both device and location as we did above:

SELECT DATE_TRUNC ('day', ye.occurred_at) AS day
     , ye.location
     , ye.device
     , COUNT(yu.user_id) AS users

We can replace the location and device lines with a parameter:

SELECT DATE_TRUNC ('day', ye.occurred_at) AS day
     , ye.{{series}} AS series
     , COUNT(yu.user_id) AS users

And add the parameter details below the query:

{% form %}

  type: select
  default: location
  options: [[location,location],

{% endform %}

Then select the series column as the “Group By” in the chart tool.

When the viewer lands on the report, they can choose location or device and refresh the report to change the chart. Click through to the report to explore the parameterized SQL query.

Combine Pivots and Parameters

A Quick Note About Stacked Bar Charts

Many of you have told us you'd like to keep stacked bar charts stacked. Now you can. You'll find the setting under “Formatting” in the chart tool.

Stacked Bar Charts

More Charts On The Way!

Now with more powerful charting tools in your hands, we're working to add more chart types. Stay tuned!

Drop us an email or Intercom message if you have questions or feedback about these new charting features (or anything else you'd like to see us tackle). We love hearing from you.

Get our weekly data newsletter

Work-related distractions for data enthusiasts.