Analyze and optimize formulas with the Formula Playground

The Formula Playground is a tool that allows you to test the outputs of formulas before entering them into a Metric. This feature is extremely useful when you want to troubleshoot your formulas, understand the impact of Dimension modifiers and identify the source data and Dimensions of Blocks used in your formula without affecting/losing your current view. This article covers the two modes of Formula Playground, Auto and Custom.

What is the Formula Playground?

The Formula Playground is a tool that allows you to test the outcome of a formula in a developmental environment before pushing into the live environment. This is incredibly powerful for troubleshooting formulas or identifying the output of a formula before creating a metric. There are two modes, Custom and Auto, the main difference between these modes is how they handle the structure of a metric. Auto mode defined the data type and dimensions used based upon the formulas output. In Custom mode, you define the dimensions and data type manually, while you write your formula.

How do you access the Formula Playground?

You can open the Formula Playground in three ways:

1. In your Application, the Formula Playground is always available from the foot of the left sidebar:

2. You can also access it from within a Metric, through the Formula Bar Menu.

3. When creating a Metric from a formula, Pigment automatically opens the Formula Playground if you choose Create from Formula.

How do you use the Formula Playground?

Once you launch the Formula Playground, choose whether you would like to use Custom structure or Automatic structure. If you want to define the Dimensions and resulting data type, use Custom. This is helpful for troubleshooting a particular formula for a Metric or for writing a formula where you already know the desired outcome. If you want to let the formula define the Dimensions and resulting data type, choose Auto. See the next sections for detailed information.

The Formula Playground offers all standard features you have on Metrics except Manual inputs and View management.

If you want to save the result of your formula, use the Create Metric button on the top right. It generates a new Metric with the type and Dimensions appearing in the Formula Playground.

Automatic structure mode

When you write a formula directly in a Metric, the very first step is to define the data type and to select the Dimension(s) of that Block. This comes with two constraints:

• Data type: if the resulting data type from the formula does not match the Metric’s data type, Pigment returns an error.

• Dimensions: if the formula's Dimensions don't match the Block Dimensions, Pigment runs an automated hidden Dimensions alignment. Pigment attempts to add missing Dimensions and make other fixes, showing any action taken or warnings to note in the Explanatory Hints and Dimensionality Hints.

With the Formula Playground in Automatic structure mode, those two constraints are removed. You can evaluate a formula as it is, without having to know the type or Dimensions beforehand.

The Formula Playground defines the data type and the Dimensions based upon your formula. This way you can very easily play with the Formula Playground and check how your Dimension modifiers impact your Block Dimensions.

ℹ️ Note

When opening the Formula Playground from an established Metric, it retains the values of a formula result but does not show any manually input items or overrides.

Custom Structure mode

Custom structure mode allows you to define the Dimensions and data type for a formula. Using the drop-downs to the right of the mode selector, you can easily adjust the data type, as well as Dimensions. This can be used to help you troubleshoot formulas without impacting the rest of the Application. You can also easily adjust the dimensionality to see the impact on your results.

Comparison Mode

Use this mode to see cells affected by formula changes without duplicating Metrics for comparison.

Access

You can access Comparison Mode in three ways:

From the Formula Playground button

1. In your Application, select the Playground button from the bottom of the left sidebar.

2. Turn on the Comparison mode toggle at the top of the Formula Playground. A dialog opens and prompts you to Add a reference Metric.

3. Under Metric name, select the Metric to compare from your Application.

4. (Optional) Update the selected Scenario. The Default Scenario is selected automatically.

5. Select Add.

From the Formula Playground when a formula is loaded

If a formula is already loaded in the Formula Playground, turn on the Comparison mode toggle at the top of the Playground.

From the Formula Bar Menu

In a Metric, open the Formula Bar Menu, then select the three-dot menu to the right of the formula bar. Select Compare in Formula Playground.

How to use Comparison Mode

When you launch Comparison mode as above, an editable formula bar labelled Draft Formula appears alongside your original formula. A second set of output data also appears beside your original output. Changes you make in the Draft Formula bar are reflected in the second output set. Where the formulas create differences, a column of Booleans opens to the right, highlighting every cell where there's a difference.

Capabilities

In this mode, you can:

• Modify the draft formula to see which cells are affected and compare the output.

• Select Performance Insights next to the Comparison mode toggle to evaluate the performance impact of your changes.

• Remove the default filter to view all data, including data that is identical between the two formulas.

• View manual inputs from the original Metric for reference. They are read-only and are not included in the draft formula output.

⚠️ Important

You cannot manually enter data into the draft formula.

To replace the original formula with the draft formula, select Replace with draft formula in the top-right corner of the pane.

ℹ️ Note

Comparison mode supports the following data types:

• Number

• Integer

• Date

• Text

Formula Wizard

The Formula Wizard guides you step by step through using the BY Modifier to create a Metric from Transactions list data.

🎓 Pigment Academy

Explore our Academy activities to practice using the Formula Playground and the Formula Wizard.