Calculated columns allows you to do math between 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 Transforms button, then select Calculated Columns from the list of transforms.
Give your new column a name.
In the next field, start typing an expression for your new column. Grow will guess what you are typing and show you options for columns or functions you can use.
If you use an expression (such as the ones listed below), red text will appear below the expression field. These are suggestions for what it expects the next part of the expression should be. If you have any errors in your expression, they will appear there, too.
If you need to stitch two strings or pieces of text together, you can concatenate them in calculating columns. This is done by using || (two pipes) in between the strings you would like to concat. Learn more about this at the bottom of this article.
Once you have added a calculated column, click done and your new column will be there. And that’s it!
Expressions in Calculated Columns
Calculated columns can handle order of expressions (things inside parenthesis are done first etc) as well as all the standard math functions. + - / *
Here is a list of supported expressions you can use while creating your custom calculations:
Expression: gt(value1, value2)
This returns TRUE if the first argument is greater than the second, and FALSE otherwise. Equivalent to the > operator.
Expression: gte(value1, value2)
Returns TRUE if the first argument is greater than or equal to the second, and FALSE otherwise. Equivalent to the >= operator.
Expression: lt(value1, value2)
Returns TRUE if the first argument is strictly less than the second, and FALSE otherwise. Equivalent to the < operator.
Expression: lte(value1, value2)
Returns TRUE if the first argument is less than or equal to the second, and FALSE otherwise. Equivalent to the <= operator.
Expression: round(value, # decimal places)
Rounds the numbers from the column you specify to the number of decimal places you choose.
Expression: substring(string, starting point, ending point)
Returns the substring from the values you specify.
Expression: DateFormat(field, format)
This will show you the date in a different format.
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.
Expression: __rowSum
Gives you a sum of the cells for each row.
Please note that the first two characters in this expression are two underscores (before rowSum), not just one.
Expression: __rowAvg
Averages the cells for each row.
Please note that the first two characters in this expression are two underscores (before rowAvg), not just one.
Merge data into one column (concatenate strings)
Enter a field name separated by the double pipe || and then the column name you want to add together. You can also concatenate custom text by wrapping the text in 'single quotes'.
{field name} || {field name} || 'text'
If you want a space in-between the variables, make sure to include a space between two single quotes, like this:
{field name} || ' ' ||{field name}