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/elseconditions 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.
