Using the Calculated Columns Transform

Calculated columns allows you to perform mathematical operations on two or more fields, change the format of a date, or extract a substring to display valuable information on your table charts. You can do this without knowing SQL, or using custom fields in the data source.

Creating Calculated Columns

To create a new calculated column, click on the +Add Transform button, then select Calculated Columns from the list of transforms.

Give your new column a name in the Column Title field. Your column title must be unique from other columns in your table of data.

To use available functions in the Column expression field, you can either click on the function button, or start typing an expression for your new column. Grow will show an autofill with options for columns or functions based on what you are typing.

Once you have added a calculated column, click Run and your new column will populate in the table of data. If you want to reference a previous calculated column, hit Run after creating the first one, and then you can reference that new column name in another calculated column.

Expressions in Calculated Columns

Please note that calculated columns respects Order of Operations when running the expression.

Here is a list of supported expressions you can use while creating your custom calculations:

Expression: __rowSum

Returns the sum of all numeric values in a row.

Please note that the first two characters in this expression are two underscores, not just one.

Expression: __rowAvg

Averages all numeric values in a row. Blank cells are not included in the average calculation.

Please note that the first two characters in this expression are two underscores, not just one.

Expression: Round([column_name], # decimal places)

Rounds the numbers from the column you specify to the number of decimal places you choose. You can even use zero to round to the nearest whole number.

Expression: SubString([column_name], starting point, ending point)

Returns the substring from the values you specify. The first value is the number of characters from the beginning of the string and the second value is also counted from the beginning of the string.

Note that this is slightly different from the SUBSTRING() formula used in PostgreSQL where the second value is the number of characters to return.

Expression: DateFormat([column_name], format)

Many times the date will come in from an integration or a database in a format like this: 2018-04-13T16:30:06+0000. Grow can read this format without any problems when you use it with other transforms, but you can use the DateFormat expression to help make it much easier to use and to get details like the full name or an abbreviation of the day or month.

Below is a list of the date/time tokens you can use in a Calculated Columns expression. You can type in regular text or combine multiple tokens in the same expression. (For example, 'YYYY-MM' to get '2019-07')

Here are some examples using these tokens:

1. Date in YYYY-MM-DD format: DateFormat([created_date],'YYYY-MM-DD')
2. 12-Hour Time (including AM/PM): DateFormat([created_date],'hh:mm:ss A')
3. 24-Hour Time: DateFormat([created_date],'HH:mm:ss')
4. Day of the week: DateFormat([created_date],'dddd')
5. Month: DateFormat([created_date],'MMMM')
6. Quarter: DateFormat([created_date],'Q')
7. Week of the Year: DateFormat([created_date],'w')

Expression: RegexExtract(/regular expression/, [column_name])

Extracts matching substrings according to a regular expression.

Expressions: GT([column1], [column2]),�GTE([column1], [column2]), LT([column1], [column2]), LTE([column1], [column2])

These expressions return "TRUE" or "FALSE" based on the comparison of the two values. The four options are "GT" (greater than), "GTE" (greater than or equal to), "LT" (less than), and "LTE" (less than or equal to). You will reference two different columns in the expression, and it would read left to right. This expression basically asks the question "Is value1 greater than/less than/or equal to�value2?"

In the example illustrated below, it compares Kevin's average monthly sales to the team's overall monthly average sales amount:

Expression: POW([column_name],exponent)

If you need to multiply the values in a column by an exponential power, you can use the POW() expression. The syntax for this is just the column name and the exponent separated by a comma:

Expression: Upper([column_name]) or Lower([column_name])

Using the upper or lower expression will transform the selected column of data into all upper case or lower case characters. This can be useful when creating uniformity in your data, especially among different data sources.

Concatenating Strings

You can also concatenate strings in the Concatenate transform with some added functionality beyond what is available on the Calculated Column transform.

To combine data from one or more columns or with any custom text, enter a column name separated by the double pipe || and then the other column name you want to add together. You can also concatenate custom text by wrapping the text in 'single quotes':

[column_name] || [column_name] || 'text' 

If you want a space in-between the variables, make sure to include a space between two single quotes, like this:

[column_name] || ' ' ||[column_name]

Finding the Number of Days Between Two Dates

You can simply subtract the end date column from the start date column to get the number of days between two dates:

Aggregations

You can use simple aggregations in calculated columns to find the sum, count, average, minimum or maximum of a column. Please note that you can also use the Aggregates transform to run the same calculations.

To run the expression simply wrap the column name in the aggregation: