Filters and cross-filters¶

Exercise filters-and-cross-filters-annex.pdf · PDF

You open the Steam store and search for a game. Next to the results there is a panel: narrow by price, narrow by tag. You tick one box and the whole list changes. Not one game. All of it.

Your Steam market dashboard says the median price is 4.99. But our indie studio hopes to make a hit. Is 4.99 the median price of hits? Nobody knows: the dashboard only answers for the whole market. Do you build a second dashboard for hits? A third for 2018? No. You keep one dashboard and let the reader choose the slice.

One control, every chart reacts¶

A filter (Superset calls it a native filter) is a control on the dashboard that lets the reader keep only some of the rows. Every chart it reaches is drawn again with those rows only.

It is the panel of the store. One control on the side, and everything on the page obeys it.

Filters live in the filter bar, the strip on the left of the dashboard. Add the first one:

Look at the top row before and after:

There is the answer for the studio. Among the hits, the median price is 9.99: twice the market median. And almost a third of the hits are free.

The hidden WHERE¶

How did the median jump from 4.99 to 9.99? Open the three dots of the Games KPI → View query. Superset shows the SQL behind the chart, and there is a new line in it:

WHERE owners_tier IN ('Hit')

You have written that clause by hand since Unit 1 (SQL Lab: filters). A dashboard filter is nothing more than a WHERE that Superset writes for the reader, into the query of every chart it reaches.

flowchart LR
    R["<b>Reader</b><br>chooses Hit,<br>presses Apply filters"] --> F["<b>Filter</b><br>Owners tier"]
    F -- "adds WHERE owners_tier IN ('Hit')" --> Q1["<b>Query</b> of KPI Games"]
    F -- "adds the same WHERE" --> Q2["<b>Query</b> of KPI Median price"]
    F -- "adds the same WHERE" --> Q3["<b>Query</b> of the line chart"]
    Q1 --> D[("<b>Database</b><br>games_clean")]
    Q2 --> D
    Q3 --> D

You can prove it without the dashboard. Run the four KPIs yourself in SQL Lab with that WHERE:

SELECT COUNT(*) AS games,
       ROUND(100 * AVG(is_free::int), 1) AS free_pct,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY price) AS median_price,
       COUNT(*) FILTER (WHERE owners_tier = 'Hit') AS hits
FROM games_clean
WHERE owners_tier IN ('Hit');

The same numbers as the dashboard. No magic: a query.

Two kinds of filter: Value and Time range¶

A Time range filter needs more care, because of how the end works. To keep the games of 2018:

Read the two labels again: the start is inclusive, the end is exclusive. The start date is in. The end date is out. So "all of 2018" ends on 2019-01-01, not on 2018-12-31. In SQL it is the pair you already know:

WHERE release_date >= '2018-01-01' AND release_date < '2019-01-01'

If your Games KPI is close to 8,845 but not equal, your dates are off by a day.

Scope: who obeys the filter¶

With 2018 applied, look at the line chart of games per year. It now has one single point. A history chart with one year of history is useless.

Should every chart obey every filter? No.

The scope of a filter is the set of charts it reaches. A chart outside the scope ignores the filter completely.

In the store, the price panel changes the list of results, but not the banner at the top of the page. The banner is out of scope.

flowchart LR
    F["<b>Filter</b><br>Release date = 2018"]
    subgraph IN ["<b>In scope</b>: recalculated for 2018"]
        K["The four KPIs"]
        P["Price band chart"]
    end
    subgraph OUT ["<b>Out of scope</b>: unchanged"]
        L["Line chart<br>games per year, 2004 to 2018"]
    end
    F -- "adds its WHERE" --> IN
    F -. "ignored" .-> OUT

Dependent filters: a list that shrinks¶

Add a Value filter named Publisher on the column publisher. Open it and scroll. The list never ends: the table has 13,951 different publishers.

SELECT COUNT(DISTINCT publisher) AS publishers,
       COUNT(DISTINCT publisher) FILTER (WHERE owners_tier = 'Hit') AS with_a_hit
FROM games_clean;

Only 336 of them have ever published a hit. If the reader has already chosen Hit, why show the other thirteen thousand?

A dependent filter is a filter whose list of values is cut down by the choice made in another filter.

Imagine the panel of the store doing the same: once you tick a genre, the list of publishers only offers those that have a game in that genre.

Two details. First, the option Values are dependent on other filters only appears when the dashboard already has another filter. Second, why 1000 options and not 13,951? A filter loads at most 1,000 values, in alphabetical order. Without the dependency, Valve is not even in the list. With Hit chosen, it is. Valve is the publisher with the most hits: 22.

Cross-filtering: the chart is the control¶

So far you, the author, decided which questions the reader can ask. But a reader looking at the pie of price bands may wonder: what about the free games only? There is no filter for that. Do they have to ask you?

Cross-filtering means clicking on a part of a chart (a slice, a bar) to filter the other charts of the dashboard by that value.

In the store, it is clicking a tag on a game's page: no panel, you just click what you are looking at.

Under the hood a cross-filter does the same as a filter: it adds a WHERE to the other charts. The difference is who is in charge.

The 2018 trap: a true number, a false story¶

Put the four slices side by side:

Look at the 2018 column. That year has a third of all the games in the table (8,845 of 26,685) but only 37 of the 623 hits. Were the games of 2018 worse?

No. They were younger. A game released in December 2018 had only weeks to find owners when the data was collected. A game from 2012 had six years. The filter changed the year, and something else changed with it without telling you: the time each game had to sell.

The number 37 is true. The story "2018 games are worse" is false. The filter gave you both.

Two habits protect you:

• Say what was filtered before you quote the number. Not "the median price is 9.99", but "among the 623 games with at least a million owners, half cost 9.99 or less, twice the median of the whole market".
• Ask what else changed with the filter. A slice is rarely different in one way only.

Key takeaways¶

• A filter is a control in the filter bar that lets the reader choose a slice. Nothing changes until Apply filters is pressed.
• A filter is a hidden WHERE: every chart in its scope runs its query again. Check it with View query.
• Value filters choose categories; Time range filters choose dates, with an inclusive start and an exclusive end.
• The scope decides which charts obey. Take a chart out when the filter would make it useless, like the history line.
• A dependent filter shrinks its list according to another filter. Cross-filtering lets the reader filter by clicking a chart.
• A filtered KPI can be a true number with a false story: say what was filtered, and ask what else changed with it.

Quick check¶

Your dashboard now answers many questions instead of one. But so far only you can see it. Next: Publishing and permissions, or who gets to open your work.