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:
Returns the sum of all numeric values in a row.
Please note that the first two characters in this expression are two underscores (before rowSum), not just one.
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 (before rowAvg), 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')
||(AM or PM) AM, PM|
||(am or pm) am, pm|
||(Hours 24) 01..023|
||(Hours 24) 1..23|
||(Hours 12) 01..12|
||(Quarter) 1st, 2nd||
||(Hours 12) 1..12|
||(Quarter) 1, 2||
||(Day of Year) 001, 002||
||(Day of Year) 1st, 2nd||
||(Day of Year) 1, 2||
||(Timezone offset) -0700|
||(Day of Month) 01, 02||
||(Timezone) MST, PST|
||(Day of Month) 1st, 2nd|
||(Day of Month) 1, 2|
||(Day of Week) Sunday, Monday|
||(Day of Week) Sun, Mon|
||(Week of Year) 01, 02|
||(Week of Year) 1st, 2nd|
||(Week of Year) 1, 2|
||(ISO Week of Year) 01, 02|
||(ISO Week of Year) 1st, 2nd|
||(ISO Week of Year) 1, 2|
Here are some examples using these tokens:
- Date in YYYY-MM-DD format:
- 12-Hour Time (including AM/PM):
- 24-Hour Time:
- Day of the week:
- Week of the Year:
Expression: RegexExtract(/regular expression/, [column_name])
Extracts matching substrings according to a regular expression. To understand better how to use RegexExtract in Grow's environment, check out our help article here.
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:
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.
Merge data into one column (concatenate strings)
NOTE: you can also concatenate strings in the Concatenate transform with some added functionality beyond what is available on the Calculated Column transform. To learn more about the Concatenate transform, click here.
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]
Find 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:
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: