5.3 Formula - Example & Tips

Overview

From each sub page of Explore Data, you can create a Formula column. Formulas are expressions that must contain data columns, and can include constants, functions, and operators.

  • Columns are values that come from the data.
    • Their names are are enclosed in square brackets, like [ShippedDate].
    • Depending on their data type, they may be used as text strings, numbers, and date/time values.
  • Constants are simply specific fixed values that you enter into a Formula.
  • Functions return values, usually computations based on columns and constants.
  • Operators do arithmetic and logical comparisons.
    • Logical comparisons must be used with Case statements.

Check Formula Help for supported Functions and Operators.

 Select Defects under Explore Data section to open the Defect Data page.

 Select Formula to start build a new formula column.

In this example, we want to build a formula column named "Defect Severity", and we want to categorize High, Fatal defects into Critical, Major, Average into Medium and others into Low.

  • Insert a column: select the Severity 
  • Formula: input the following expression and click Add button.

case [Severity]
when 'High' then 'Critical'
when 'Fatal' then 'Critical'
when 'Major' then 'Medium'
when 'Average' then 'Medium'
else 'Low'
end

View the new column labeled "Defect Severity" displayed as the last column in the data table.

 

TIPS

1) To do decimal arithmetic, you must explicitly cast the column data type to be a float instead of just an integer 

For example: To calculate percentage of not-passing runs, formula like "[Runs not passing]/[Total Run Logs]" will not work, you should use the following:

(Cast([Runs not passing] as float)) / (Cast([Total Run Logs] as float))

 

2) Explicitly define the date format in date_part function.

For example: To get the year "2016" from a date "2016-10-07", use following function, note that you need to specify the right date format (in this example: YYYY-MM-DD)

date_part('year', to_date([Created Date], 'YYYY-MM-DD'))