Lesson 2 of 8 · Lab

Add a what-if parameter to a dashboard

A Vista Parameter is a value the dashboard's reader sets, such as a threshold. Bring it into the flow, and every number built on it changes when the reader changes it.

"Short" needs a line: a share of the bikes a shop owns. Where to draw it is Marit's call, so give her the control.

This live flow in Strata, Oplema's browser app for data prep, starts from the daily table of fictional Kramerica Bikes in a Manual Input, with each shop's bikes_owned added: Harbour owns 34 bikes, Station Road 46 and Old Town 16. Formula counts a day as short only when every bike is out.

Harbour's busiest day had 34 bikes out, all it owns. Does that make Harbour the shop that's short?

Not on its own. That Friday, September 18, Harbourlight Ferries' staff outing took 26 bikes in one booking. Harbour's other Fridays had 24 out. Count the days, not the one day Tomas remembers.

Bring the line into the flow

Wire the Parameter into the flow, and change Formula's rule so a day is short when bikes out reach value percent of bikes_owned.

Check, at 70: Harbour 12, Station Road 4 and Old Town 24 short days. Lookup's Every Row puts the one value on every day.

If you're stuck
  1. Lookup is selected and set to Every Row: it adds the lower table's columns to every row of the upper one. Its lower input is empty.
  2. Drag from Vista Parameter's right dot to Lookup's lower left dot. Every day gains a value column holding the Parameter's setting, 70.
  3. Select Summarize. With every bike out as the line, Harbour has 1 short day, Station Road 0 and Old Town 4.
  4. Select Formula. After bikes_owned in the expression, type * value / 100, then select the Formula block.
  5. Select Summarize again: Harbour 12, Station Road 4 and Old Town 24 short days at 70%.

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 the expression now says

CASE WHEN bikes_out >= bikes_owned * value / 100 THEN 1 ELSE 0 END gives a day 1 when bikes out reach the chosen share of the fleet, and 0 otherwise. Summarize adds the ones up. Any yes-or-no rule can be counted this way.

Let Marit draw the line

Below, the same flow is published to Vista, Oplema's browser app for dashboards, with the Parameter as three buttons.

  1. Select 80%. The bars read 9 for Harbour, 2 for Station Road and 15 for Old Town.
  2. Select 90%: 4, 0 and 9.
  3. Select the buttons' frame. Under Data, Allowed values is Allowed list: 70, 80 and 90. Typing in Value does nothing while a list is set.

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

Short days in September, by the share of the fleet out
ShareHarbourStation RoadOld Town
70%12424
80%9215
90%409

Marit chooses 90%: a shop is short on a day when 90% of its bikes are out.

So the answer to Marit's first question is Old Town: not Harbour, whose fullest day was one ferry outing, and not Station Road, which never had 90% of its bikes out.

It's an agreement, not a measurement. The till records hires, not the people turned away, so a short day means bikes ran low, not that anyone left without one. The 7 undated hires sit on no day, so these counts can only be low.

Your line for Marit: "At 70, 80 or 90%, Old Town is short most often. At 90%, that's 9 days, against Harbour's 4."

See the Vista Parameter and Lookup references and the Parameters and filters guide. For later: KPI vs metric covers writing down what a measure means.

Common parameter mistakes

  • Letting the reader type any value. An allowed list keeps to the values you've checked.
  • Forgetting the wire. A Parameter changes nothing until a block reads it.
  • Leaving the definition off the page. A count of short days means little unless the page says what short is.

Parameters in SQL and Excel

SET VARIABLE share = 90;
SELECT shop,
  sum(CASE WHEN bikes_out >= bikes_owned * getvariable('share') / 100
      THEN 1 ELSE 0 END) AS short_days
FROM daily GROUP BY shop;

In DuckDB a variable plays the Parameter's part. In Excel, put the share in a cell with a Data Validation list and point COUNTIFS at it.