Today, we’re launching Calculated Fields to improve your workflow, extend your analysis, and explore data faster.

See how it works:

What exactly is a calculated field?

A calculated field is a field that allows you to create new data from your existing data by applying additional logic to existing fields. For example, imagine that you have a simple table with three columns: date, ID, and sign up (as shown below). In this case, the metric "sign-up rate" doesn’t exist, but you can create it using Calculated Fields.

Simply write a formula to create a new field called “sign-up rate” by taking total sign-ups, and dividing them by sign-ups on specific dates, as you can see on the right.

You can then drag Calculated Fields into your charts and visualizations and use them to filter, just as you would a SQL generated field.

Why we built it: Calculated Fields make it easier to explore large datasets

For analysts, SQL is the most powerful and flexible way to answer specific questions, like “How did revenue from this quarter compare to this quarter last year?”

But to answer complicated, ambiguous questions, like, “How should we change our pricing model based on the change in demand from the pandemic?” using just SQL, is tedious and time-consuming. It requires you to:

• Determine measures and dimensions before running the query, which makes your queries more complicated.
• Write multiple queries to create different charts and visualizations, as queries are likely constrained to calculating one metric.
• Parse through lengthy queries, make changes, and re-run them against your database to explore data.

Last year, we released Helix, our in-memory data engine, which automatically streams up to 10GB of data without overloading your database. We built Calculated Fields to help you more easily explore these large datasets without going back into SQL queries.

Calculated Fields provide a way to explore the data by allowing you to create new metrics and build new charts and visualizations, all post-SQL.

Getting started with Calculated Fields in Mode

So, when would you want to use Calculated Fields?

• When the metrics you need for your analysis are not directly stored in your data warehouse.
• When you want to clean up values for your visualization.
• When you want to quickly aggregate and filter your data.

To apply Calculated Fields, first run a SQL query and open up the Chart editor. Look for the new Calculated Fields button in the top right corner.

Click the button, and a modal will pop up. Begin typing a formula or scroll to the panel on the right to find a full list of functions that Mode currently supports.

Once you’ve finished your formula, you can see instant formula validation at the bottom of the box, letting you know either that it’s valid or it contains an error.

For a full walkthrough, watch our Calculated Fields tutorial video and head on over to the Help site for resources on getting started, best practices, formula recipes, and more.

Work faster and extend your analysis with Calculated Fields

Calculated Fields saves hours of tedious work by allowing you to write and run fewer SQL queries, as well as quickly iterate on them. You can now use the same query to answer hundreds of questions, instead of editing lengthy SQL queries—making your workflow more efficient and delightful.

Try Calculated Fields

New to Mode? Sign up for a free Mode Studio account Or log in to your Mode account