Key Concepts II: Creating filters, sorting, and counting rows

In this section, we'll guide you through the basic ways of exploring your data and finding answers to questions.

Specifically, we'll take you through:

  • Creating filters
  • Sorting
  • Counting rows.

These are basic, but fundamental, actions that you'll use frequently in Trevor.

As we did in our guide to understanding your database, we'll use a fictitious company called GBC, selling ridiculously expensive giant beanbag chairs, to provide context for examples.

Let's jump right in!

Creating filters

You can use filters to identify and isolate data based on some specific attribute.

Here's an example question:

How many of our customers are over the age of 25?

To answer it, let's assume we have a nice 'ole customer table that contains this information, like so:

nice-ole-customer-table

To find and isolate customers over the age of 25, we would simply:

  1. Click the header of the Age column.
  2. Select Filter by Age, from the 3 options (Filter, Sort, Count) that appear.
  3. Add our filter i.e. > 25.

Click save and Trevor will run the query and limit the results to customers who are older than 25. Done. Weehoo!

Depending on the type of data you're looking at, you can create different types of filters. For example, if you were looking at the Sales table and it contained the date on which each sale was made, you could create a filter to look at sales between two different dates (e.g. between the 1st March and 1st April), or on a rolling basis (e.g. within the last 7 days). If it contained the country in which each sale was made, you could create a filter to include, or exclude, specific countries.

It's easier than ever to create a filter in Trevor, and really useful for finding and isolating what you're looking for.

Moooving on!

Sorting

Sorting lets you put the data in ascending or descending order.

Simply click the header of the column containing the data you wish to sort, then select the Sort by option.

You can also sort the data based on multiple columns. For example, consider the following table, which contains data about GBC's sales that hasn't been sorted:

unsorted

There may be some interesting takeaways in there, but it's a bit of a mess and so hard to make sense of.

Now, consider the following:

unsorted

This is exactly the same table - the only difference is that we've sorted the data by the values in the Country column and then by the values in the Count column. It's now 10x easier to make sense of and derive meaning from.

To do this in Trevor, you would:

  1. Click the Country column header.
  2. Select Sort by Country.
  3. Click the + icon in the modal that pops up, and then choose the Sales column.

Done!

Counting rows

Counting rows lets you count the number of rows that contain the distinct values of a specific column. It's a great way to see a summary, or breakdown, of your data in terms of one or multiple attributes.

Here's an example question:

What's the breakdown of our customers by country?

So let's say that in our nice 'ole Customer table we have a Nationality column:

counting-rows

To see a breakdown of our customers by nationality, we would simply:

  1. Click the Nationality column header.
  2. Select Count rows by Nationality.

Done!

The results will show the number of times that each distinct nationality appears in the nationality column, thereby giving us the breakdown we were looking for. It might be something like this:

counting-rows

*word is that Canadians LOVE giant beanbag chairs.

If appropriate for your data, Trevor will visualise the results in a bar or line chart.

Just like with sorting, you can also count rows by multiple columns. Take a look at this table:

unsorted

(Look familiar? We used it a few minutes ago when explaining how to sort data by multiple columns!)

This table, which shows the sales of specific products in specific countries, is actually what you would see if you counted the rows in a table by two columns. On the left, we have the first column we selected, in the middle we have the second column, and on the right we have the result.

Note: your data isn't sorted automatically - remember to sort it after counting rows, if it makes sense!


You've reached the end of this section! In the next section, we talk about joining tables together.

Harry Marshall

Read more posts by this author.

Subscribe to Trevor Blog

Get the latest posts delivered right to your inbox.

or subscribe via RSS with Feedly!