Formula Column
In addition to the base columns a table has, you can add a new column with a custom formula based on one or more of the table's other
columns. For example, say you have two columns for 'Sales' and 'Cost'.
You can define a custom formula 'Sales' - 'Cost' and add it as a formula column called 'Profit'. That's a simple example with the
difference function. There are a lot more functions listed based on type
- Date, Numeric, String, Aggregate, Statistical and General. Let's see
examples from each of these function sets.
Date: You can manipulate values of a given date column.
Example:
Let's say you have a column that has date values like 2011-01-01
12:30:48 (or say Jan 30, 2011). And you want to have a column that lists the corresponding day of the week. You can define the formula as weekday("Date").
Let's see another scenario for a date specific formula column. Say you want the number of days between a lead coming in and the sale getting closed. Or say, the number of days it takes to close a support ticket. The formula to be used would be datediff("Date1", "Date2").
Numeric: Numeric formulas ranging from absolute value to mod to log to exponential to trigonometric functions like sin-cos-tan are all handled here.
Example
: Let's say you have some test data values in three columns and you want to list the greatest value of each row. You can define the formula as greatest("Column1","Column2","Column3")
String:
This type of formula functions deal with text values (known as 'string'in programmer lingo). Knowing the length of a given string, string concantenation (joining of two textual values), finding a substring, upper/lower case conversions etc are all dealt with here.
Example:
Here's a scenario. In one of the Date functions above, we saw weekday(date). What if you want only the first three characters of the weekday to be displayed? You can define the formula column like
left(weekday("Date"), 3)
Aggregate & Statistical : Functions like count, sum, average, variance, standard deviation, mean, median, mode etc
Example:
Suppose you want to calculate the column's average and find the difference for each row. You can have the formula as Column1 - Avg ("Column1"). Or say you want to calculate the percentage of a sale value to overall sales. The formula will be ("Sales"/Sum("Sales"))*100
General: If and ifnull functions.
Example:
There are instances where you won't like to have a null value. In such
a case you can have a formula defined something like ifnull(null, 0). All
cells with null values will get replaced by zero. Consider another scenario. Say different product categories attract different taxes. We can calculate the tax with the formula if("Product Category"='Stationery', "Sales"*0.05, "Sales*0.08). This calculates the sales tax for Stationery as 5% and for other product categories as 8%. We can even have nested if conditions. Like if("Product Category"='Stationery', "Sales"*0.05, if("Product Category"='Furniture',"Sales"*0.10,"Sales"*0.08))
In addition to the base columns a table has, you can add a new column with a custom formula based on one or more of the table's other
columns. For example, say you have two columns for 'Sales' and 'Cost'.
You can define a custom formula 'Sales' - 'Cost' and add it as a formula column called 'Profit'. That's a simple example with the
difference function. There are a lot more functions listed based on type
- Date, Numeric, String, Aggregate, Statistical and General. Let's see
examples from each of these function sets.
Date: You can manipulate values of a given date column.
Example:
Let's say you have a column that has date values like 2011-01-01
12:30:48 (or say Jan 30, 2011). And you want to have a column that lists the corresponding day of the week. You can define the formula as weekday("Date").
Let's see another scenario for a date specific formula column. Say you want the number of days between a lead coming in and the sale getting closed. Or say, the number of days it takes to close a support ticket. The formula to be used would be datediff("Date1", "Date2").
Numeric: Numeric formulas ranging from absolute value to mod to log to exponential to trigonometric functions like sin-cos-tan are all handled here.
Example
: Let's say you have some test data values in three columns and you want to list the greatest value of each row. You can define the formula as greatest("Column1","Column2","Column3")
String:
This type of formula functions deal with text values (known as 'string'in programmer lingo). Knowing the length of a given string, string concantenation (joining of two textual values), finding a substring, upper/lower case conversions etc are all dealt with here.
Example:
Here's a scenario. In one of the Date functions above, we saw weekday(date). What if you want only the first three characters of the weekday to be displayed? You can define the formula column like
left(weekday("Date"), 3)
Aggregate & Statistical : Functions like count, sum, average, variance, standard deviation, mean, median, mode etc
Example:
Suppose you want to calculate the column's average and find the difference for each row. You can have the formula as Column1 - Avg ("Column1"). Or say you want to calculate the percentage of a sale value to overall sales. The formula will be ("Sales"/Sum("Sales"))*100
General: If and ifnull functions.
Example:
There are instances where you won't like to have a null value. In such
a case you can have a formula defined something like ifnull(null, 0). All
cells with null values will get replaced by zero. Consider another scenario. Say different product categories attract different taxes. We can calculate the tax with the formula if("Product Category"='Stationery', "Sales"*0.05, "Sales*0.08). This calculates the sales tax for Stationery as 5% and for other product categories as 8%. We can even have nested if conditions. Like if("Product Category"='Stationery', "Sales"*0.05, if("Product Category"='Furniture',"Sales"*0.10,"Sales"*0.08))
Posts
