Understand formulas in searches

To provide richer insights, you can add a formula to your ThoughtSpot search.

The Formula Builder includes many types of operators, such as logical (if, then, else), math, date, and text string functions.

You can create a formula from directly within a search. If you have the privilege that allows you to create or edit worksheets, you can also create a formula within a worksheet. Formulas in worksheets act as derived columns, so that anyone who uses the worksheet as a data source will see the formula as just another column.

Adding a formula within a search works much the same way as adding a formula to a worksheet. However, you will be able to edit the formula directly from within the answer. If you add the answer to a Liveboard and share it with the Edit privilege, other people can see the formula results, too. To make edits to the formula, they also need to have the Edit privilege on the underlying data.

The following list of topics explain concepts and give step-by-step instructions of how to work with formulas. We also recommend that you review our comprehensive coverage of all formulas in the Formula function reference, which provides brief descriptions and examples for all supported formulas, by type.

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 if…​then…​else logic.

View or edit a formula in a search

You can always go back and view or edit a formula that has been added to a search. Do this by selecting the edit icon next to its name in the Columns listing.

About aggregate formulas

When working with formulas, it is useful to understand the difference between regular (or row-wise) formulas and aggregation formulas.

About conversion formulas

Some formulas require the input to be of a particular data type. If you find that you want to pass a value to the function, but it is of the wrong data type, you can convert it using a conversion formula.

About date formulas

Date formulas allow you to apply date related functions to your formulas.

Simple number calculations

Use simple number function (addition, subtraction, multiplication, and division) to calculate numerical results.

Percent calculations

Use number functions to perform useful percent calculations. About conditional formulas (operators) Conditional formulas, or operators, allow you to apply if/then/else conditions in your formulas.

About nested formulas

Nested formulas, or formula on formula, allow you to reference a formula inside another formula.

About formula support for chasm trap schemas

You can create a formula that involves aggregated measures coming from multiple fact tables of a chasm trap.

Text formulas

ThoughtSpot also uses text formulas.