ThoughtSpot formula examples

Customizing your data with formulas

You can use the ThoughtSpot formula editor create a formula either within a single answer or as part of a worksheet. To create a formula at the answer-level, you can extend the default worksheets. However, if you want to use a formula across multiple answers, you might need to copy it across each answer. Alternatively, creating a worksheet-level formula in a custom worksheet gives you access to all formulas across a set of answers. You can also use a custom worksheet to add unique filters to a Liveboard.

The following examples demonstrate general and Turbonomic-specific formula operations that you can extend to your reports.

Mapping and filtering qualitative data

Qualitative data, labeled as ATTRIBUTE in ThoughtSpot, includes string columns for values such as entity ID, metric type, and datetime.

Aggregating and converting quantitative data

Quantitative data, labeled as MEASURE in ThoughtSpot, includes columns such as used, capacity, and savings. For quantitative columns, you can use number keywords in the search bar for quick aggregation. However, sometimes you might need to use formulas to access more complex logic.

• Converting units: When a certain column contains values that you want to convert to another unit, enter the conversion as a mathematical formula.

  For example, the following formula converts a column_with_km column with values in kilometers to meters:

  column_with_km / 1000
  

  For better maintainability and readability, you can replace the numerical value with a parameter.

  For example, you can create a parameter that is called km_and_m_conversion, with a value of 1000, and use it in this formula:

  colmn_with_km / km_and_m_conversion
  

  For more information, see Simple number calculations and Type conversion functions in the ThoughtSpot documentation.

• Aggregating with conditions: The following formula template shows how to aggregate a data sum:

  sum_if ( ${some condition} = '${desired condition}' , ${data column} * ${Optional unit conversion calculation} ) 
  

  For example, the following formula uses a parameter to convert any carbon metric values from grams to kilograms and then sums the results:

  sum_if ( metric_type = 'carbon' , used / kg_and_g_conversion ) 
  

  This formula first checks whether the metric_type= 'carbon' condition is met and then divides the used carbon value by a preconfigured kg_and_g_conversion parameter, with a value of 1000, to convert from grams to kilograms. It then aggregates the converted values that met the condition.

• Aggregating without conditions: The following formula adds all the values under value with the container_cluster_id

  group_sum ( value , container_cluster_id )
  

• Aggregating by group or filter: Group aggregation is a powerful formula to aggregate columns and values together given specific filter and group conditions. The formula uses the following format:

  group_aggregate( [aggregation type] ( [column] ), [groupings], [filters] ) 
  

  Grouping options:

  • query_groups(): Dynamically adds the attribute columns from the answer
  • { col1, col2, ... coln }: Static list of attribute columns
  • + or -: Add or remove column sets
  Filtering options:

  • query_filters(): Dynamically adds the filters from the answer
  • { boolean_condition }: Boolean condition based on static list of filters
  • + or -: Add or remove filter sets

  If no modifications from the groups and filters in the query are needed, then query_groups( ) and query_filters ( ) can be omitted.

  Because the formula is the most flexible format, you can use it for a simple aggregation. For example, a simple average based on the Used value, which uses the groups and filters in the answer:

  group_aggregate( average(Used), query_groups(), query_filters() ) 
  

  Or you can perform more complex logic. For example:

  sum ( group_aggregate ( sum ( savings ) , query_groups ( ) + { [id] } , query_filters ( ) + {[type] = cloud} ) ) * tree_sequestration_factor / metric_ton_to_g 
  

  In this example, you obtain the sum of all savings among the IDs, which are shown as {[id]}, and anything the user set within the query, which is shown as query_groups( ). This formula also filters the type as cloud, which is shown as {[type] = cloud}, and anything the user set within the query, which is shown as query_filters ( ).

  After the group aggregation is obtained, it is then summed all together with the outer sum().

  The formula then use parameters to multiply the aggregated sum by the tree_sequestration_factor value and convert the result from grams to metric tons.

  For more examples, see this official documentation on group aggregation and flexible group aggregation functions.

Creating a formula for average utilization of all VMs or clusters

This specific example is from the cluster_capacity_namespac_consumed default worksheet.

The utilization value is under the metric_fact table, which includes the values for capacity, used, utilization, and consumed.

This table also includes utilization calculations for cluster capacity and namespace consumed. It uses a namespace_consumed_daily view to obtain the namespace that is consumed by the day called consumed_day. It then obtains the capacity from the cluster_capacity_by_date table called capacity_hour.

The following formula calculates the utilization for the cluster:

consumed_day / capacity_hour

Creating a formula for entity metric capacity in bytes

This specific example is from the Entity Metrics default worksheet.

Similar to the previous example, you can obtain the capacity from the metric_fact table, under the capacity column. However, for better readability, you want to display them in either KB, MB, or GB.

1. Create a parameter called Units (Bytes), with the allowed values as List.
2. Enter the following conversions, with the default value as 1:

   Value: 1 Display as (optional): KB Value: 1024 Display as (optional): MB Value: 1048576 Display as (optional): GB

3. With the parameter created, you can now create the formula.
   1. Check whether the capacity is in the unit bytes. The is_byte identifier is in the metric_type_dim table.

   2. If the is_byte value is true, start the calculation:

      if ( is_byte ) then capacity / units (bytes) else capacity 
      

      This formula displays the capacity in the bytes if the capacity unit is bytes. If not, then it shows that the capacity as is.

Creating a formula for total carbon emitted and other metric_fact table values

To obtain carbon, or anything that is related to metric_fact table values, you first need to identify the carbon types by setting a carbon pre-condition from the metric_type_dim column. If so, then proceed to unit conversions:

sum_if ( type = 'carbon' , used / 1000 ) 

The same logic can be applied for energy, CPU, power, GPU, and more from the metric_type_dim table.

Getting the latest data from a table

If you want to show only the latest value of all the time series data, set the datetime_id to itself.

if ( ${some conditions} = '${some desired state}' and datetime_id = datetime_id ) then true else false 

For example, you can create the following formula and name it Latest Data:

if ( state = 'ready' and datetime_id = datetime_id ) then true else false 

Alternatively, you might use datetime instead of datetime_id:

if ( datetime = group_max ( datetime ) ) then true else false 

You can add as many conditions as you need.

Then, use the formula in a ThoughtSpot query to show all data with the latest value:

... Latest Data = true ...