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 "Column Title" field.
In the "Column Expression" 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 concatenate. 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. 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
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: __rowSum
Gives you a sum of the cells that contain numbers 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 that contain numbers for each row.
Please note that the first two characters in this expression are two underscores (before rowAvg), not just one.
Expression: round(value, # 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(string, 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 SUBSTR() formula used in SQLite where the second value is the number of characters to return.
Expression: DateFormat(field, 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')
| Token | Example | Token | Example |
|---|---|---|---|
MMMM |
January, February | A |
(AM or PM) AM, PM |
MMM |
Jan, Feb | a |
(am or pm) am, pm |
MM |
01, 02 | HH |
(Hours 24) 01..023 |
M |
1, 2 | H |
(Hours 24) 1..23 |
Mo |
1st, 2nd | hh |
(Hours 12) 01..12 |
Qo |
(Quarter) 1st, 2nd | h |
(Hours 12) 1..12 |
Q |
(Quarter) 1, 2 | mm |
(Minutes) 00..59 |
DDDD |
(Day of Year) 001, 002 | m |
(Minutes) 0..59 |
DDDo |
(Day of Year) 1st, 2nd | ss |
(Seconds) 00..59 |
DDD |
(Day of Year) 1, 2 | ZZ |
(Timezone offset) -0700 |
DD |
(Day of Month) 01, 02 | z |
(Timezone) MST, PST |
Do |
(Day of Month) 1st, 2nd | ||
D |
(Day of Month) 1, 2 | ||
dddd |
(Day of Week) Sunday, Monday | ||
ddd |
(Day of Week) Sun, Mon | ||
ww |
(Week of Year) 01, 02 | ||
wo |
(Week of Year) 1st, 2nd | ||
w |
(Week of Year) 1, 2 | ||
WW |
(ISO Week of Year) 01, 02 | ||
Wo |
(ISO Week of Year) 1st, 2nd | ||
W |
(ISO Week of Year) 1, 2 | ||
YYYY |
2019 | ||
YY |
19 |
Here are some examples using these tokens:
- Date in YYYY-MM-DD format:
DateFormat({created_time},'YYYY-MM-DD') - 12-Hour Time (including AM/PM):
DateFormat({created_time},'hh:mm:ss A') - 24-Hour Time:
DateFormat({created_time},'HH:mm:ss') - Day of the week:
DateFormat({created_time},'dddd') - Month:
DateFormat({created_time},'MMMM') - Quarter:
DateFormat({created_time},'Q') - Week of the Year:
DateFormat({created_time},'w')
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 the relation between the overall sales average and the individual sales average of each rep: 
Expression: POW(column,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:
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}