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 Pinboard 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.
-
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 clicking the edit icon next to its name in the Columns listing.
-
When working with formulas, it is useful to understand the difference between regular (or row-wise) formulas and aggregation 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.
-
Date formulas allow you to apply date related functions to your formulas.
-
About percent (simple number) calculations
You can use simple 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. -
Nested formulas, or formula on formula, allow you to reference a formula within 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 are also available. These are covered in the comprehensive Formula function reference, which provides brief descriptions and examples for all types of formulas. The above topics explain concepts and give step-by-step instructions of how to work with formulas, whereas the reference is a quick cheat sheet.