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.
In the "Column Expression" field, 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 (before rowSum), 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 (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')
| 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_date],'YYYY-MM-DD') - 12-Hour Time (including AM/PM):
DateFormat([created_date],'hh:mm:ss A') - 24-Hour Time:
DateFormat([created_date],'HH:mm:ss') - Day of the week:
DateFormat([created_date],'dddd') - Month:
DateFormat([created_date],'MMMM') - Quarter:
DateFormat([created_date],'Q') - Week of the Year:
DateFormat([created_date],'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 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:
Merge data into one column (concatenate strings)
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:
Aggregations
You can use simple aggregations in calculated columns to find the sum, count, average, minimum or maximum of a column. You can also use the Sum, Count, Avg, Min, Max transform to run the same calculations. Please note that aggregations are only supported on the Faster Transform Architecture.
To run the expression simply wrap the column name in the aggregation:
