Lesson 1 of 8 · Lab

Add a shop filter to a dashboard

Vista Filters holds a dashboard's filters, and each filter has a scope: the visuals it applies to. A filter that misses a visual leaves the page disagreeing with itself.

Marit read the month-end table and came back with one line: "Not a month. Show me each day, for each shop." Each manager also wants to see only their own shop.

This live flow in Strata, Oplema's browser app for data prep, holds September's finished hires at fictional Kramerica Bikes in three Manual Input blocks, one per shop. Manual Input is a table typed into the flow, so the rows travel with the lesson. Union stacks them, and Filter has set aside the 7 hires whose date says see note.

Summarize will group by shop and day. Without the Filter, how many rows would it make?

89 instead of 86. An empty day is still a group, so each shop would get one extra row for its undated hires.

Build the daily table

Make a table with one row per shop for each day it opened, holding the bikes out, and send it to Vista, Oplema's browser app for dashboards, through a Vista Source named daily_bikes_out.

Check: 86 rows × 3 columns and 1,717 bikes out. Summarize can group by two columns.

If you're stuck
  1. Filter is selected. Its rule, hire_on IS NOT NULL, keeps hires that have a date.
  2. In the palette, select Transform and drag Summarize to the right of Filter. Drag from Filter's upper right dot, its True output, to Summarize's left dot.
  3. Select Summarize. Add three actions: Group by shop, Group by hire_on and Sum of bikes. In the Sum's Output, select all (Ctrl+A or Cmd+A) and type bikes_out.
  4. The preview reads 86 rows × 3 columns: one row per shop per day it opened.
  5. From IO, drag Vista Source to the right of Summarize and drag from Summarize's right dot to its left dot.
  6. Select Vista Source, select the Display name field, select all (Ctrl+A or Cmd+A) and type daily_bikes_out. Vista can now build on this table.

Swipe sideways to see the canvas, or open this page on a larger screen to work with it.

In an expression, press Esc to apply it and move on (press it twice if a suggestion list is open).

What you should see

Harbour and Station Road have 30 days each and Old Town 26, because it closes on Mondays. The busiest days had 34, 40 and 16 bikes out.

Make the filter reach the whole page

This is that table as a dashboard: a Shop filter, the month's bikes out and a line by day. Here it's built for you. A later lesson, Publish a dashboard, builds one from scratch in the Strata app.

Select Old Town and watch which numbers move. Then make the Shop filter move every number on the page.

Check: 344 for Old Town, 649 for Harbour, 724 for Station Road and 1,717 for All shops.

If you're stuck
  1. Select Old Town. The line drops to Old Town's days, but Bikes out in September still reads 1,717.
  2. Clicking a shop also selects the filter. Applies to reads Selected visuals, and under Affected inputs only Columns / Page 1, the line, is selected.
  3. Open Applies to and choose This page. The total now reads 344.
  4. Select Harbour, then Station Road: 649 and 724. All shops brings back 1,717.

Open this page on a larger screen to edit this dashboard.

A filter's scope is part of what the page says. Selected visuals suits a deliberate exception, such as a total kept fixed for comparison, and then that visual needs a label saying so.

Your line for Marit: "Each manager can pick their own shop. On its busiest days Old Town had all 16 of its bikes out."

See the Vista Filters and Summarize references. For later: Dashboard filters covers scope and what a filter hides.

Common dashboard filter mistakes

  • Scoping a filter to some visuals by accident. The page still looks finished. Pick each choice once and check that every number moves.
  • Grouping before removing undated rows. An empty day becomes a group of its own, and a by-day chart gains a point with no date.
  • Quoting a filtered number without its filter. 344 is Old Town's month, not Kramerica's. Say which shop a number is for.

Filters in SQL and Excel

SELECT shop, hire_on, sum(bikes) AS bikes_out
FROM hires
WHERE hire_on IS NOT NULL
GROUP BY shop, hire_on;

SELECT sum(bikes_out) FROM daily WHERE shop = 'Old Town';

In SQL every query states its own filter, so two numbers on one page can easily use different ones. Excel has the same trap: a slicer filters only the PivotTables listed under Report Connections.