Add a formula to a search

You can add a formula directly within a search. Some common reasons for using a formula in a search are to perform mathematical functions, check for and replace null values, or add conditional logic.

Starting in May 2022, ThoughtSpot rebranded pinboards as Liveboards. For backward compatibility, we currently support internal processes and external methods that use the older naming convention.
The formula editor redesign is part of the new answer experience. The new answer experience is off by default, but the option to turn it on or off individually is on by default. If you have the classic answer experience on, your user interface and experience may be different from the experience described in this article. You can turn the new answer experience on or off individually from the Experience manager panel in your profile. If that option is not available, contact your administrator.

Adding a formula to a search

To create a formula in a search, follow these steps:

  1. Start a new search, or choose an existing answer from a Liveboard to edit.

  2. Open the Data panel from the upper right corner if it is not open, and click the + icon next to Formulas. If the new answer experience is off in your environment, click the more options icon more options menu icon in the upper-right side of the table, and select Add formula.

    Add a formula

  3. Name and enter your formula in the Formula Editor. The new Formula Editor supports multi-line wrapping of long formulas. To continue the formula on a new line, press Enter or return on your keyboard. The Editor displays suggestions after you enter at least one character.

    Use the Formula Builder
    Formula elements are color coded by type and can include the formula operators and functions (blue), the names of columns (purple), and/or constants (black).

  4. You can see a list of formula operators with examples by selecting the help icon ? in the upper right corner. If the new answer experience is off in your environment, click Formula Assistant. Expand a category, such as Comparison, and click on a formula operator for a definition and example.

    Formula Assistant

  5. To change what your formula returns, use the Advanced settings. Depending on your formula, you may be able to change the following aspects:

    • Data type: Determines the type of data that the formula generates. For example, use date for an add_days formula.

    • Measure or attribute: Determines if the output of the formula is a measure or an attribute. For example, choose attribute for a formula that generates age groups, and choose measure for a formula that generates profit.

    • Aggregation: Determines the default aggregation of the formula output. For example, choose min to see minimum profit.

    Advanced settings in the Formula Builder

  6. Click Save. Name your formula, if you did not already name it.

New and classic Formula Editor experiences

As part of the new Answer experience, we redesigned the Formula Editor. You may notice the following differences between the new Formula Editor, and the Formula Editor in the classic Answer experience:

New Formula Editor

  • Supports multiline formulas and indentation

  • Smoother, faster, and more bug-free

  • You see completions after you start typing. This allows users to add new lines more easily between tokens.

  • Redesigned right panel houses both the formula settings and the help panel

  • Search for specific keywords in the help panel

  • The formula operator sections in the help panel are more intuitively organized and easier to navigate

Classic Formula Editor

  • Supports only single-line formulas

  • May not be as fast as the new Formula Editor

  • You see completions before you start typing

  • The right panel houses the help panel, but the formula settings are at the lower section of the Formula Editor

  • No ability to search for specific keywords in the help panel

  • The formula operator sections in the help panel may be harder to navigate

Related information

Was this page helpful?
×