In this series of posts, you will get to know the little known, yet very powerful and useful features of Zoho Analytics. In this first part, let’s explore the ‘Formula Column’ feature.

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 concatenation (joining of two textual values), finding a sub-string, 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))

Hope you now know how to put Formula Columns to best use in Zoho Analytics! Do let us know how you use or intend to use formula columns and keep your feedback comments coming.

You can follow us on Twitter and Facebook.

1) How to calculate Turn around Time (Viz. difference of 2 columns in date and time format)? Result needed in hh:mm format.2) Is there any function similar to vlookup in xls (without using multiple IF nesting)?

1) How to calculate Turn around Time (Viz. difference of 2 columns in date and time format)? Result needed in hh:mm format.2) Is there any function similar to vlookup in xls (without using multiple IF nesting)?

chuck_sena: We are on it. Expect an update soon.Aden: It’s not possible as of now. We’ll add them soon. However, you can use the if() condition as a substitute for now.e.g., (Region=’East’ or Region=’West’) should return 1, else 0. Equivalent IF statement would be:if((Region=’East’), 1, if((Region=’West’),1,0))Hope that helps. If you have further questions, mail us at support@zohoreports.com and we will be glad to help.

chuck_sena: We are on it. Expect an update soon.Aden: It’s not possible as of now. We’ll add them soon. However, you can use the if() condition as a substitute for now.e.g., (Region=’East’ or Region=’West’) should return 1, else 0. Equivalent IF statement would be:if((Region=’East’), 1, if((Region=’West’),1,0))Hope that helps. If you have further questions, mail us at support@zohoreports.com and we will be glad to help.

Is there a way to use AND (&&) and OR (||) arguments in your formulas in formula columns? I can’t figure out how to do it.

Is there a way to use AND (&&) and OR (||) arguments in your formulas in formula columns? I can’t figure out how to do it.

Any update on when the Reports & CRM integration will be unveiled?

Any update on when the Reports & CRM integration will be unveiled?

Great post. The more column control, the better. Inline editing is a must these days.

Great post. The more column control, the better. Inline editing is a must these days.

The report modules of CRM and Support aren’t coming from Zoho Analytics now. However, we will be providing a Zoho Analytics-CRM integration soon followed by Zoho Analytics integration with other Zoho services, including Zoho Support. Once it is there, those integrated services will get almost all the functionalities of Zoho Analytics within them.

The report modules of CRM and Support aren’t coming from Zoho Analytics now. However, we will be providing a Zoho Analytics-CRM integration soon followed by Zoho Analytics integration with other Zoho services, including Zoho Support. Once it is there, those integrated services will get almost all the functionalities of Zoho Analytics within them.

We need formulas also for

reports module of CRM and

Support

We need formulas also for

reports module of CRM and

Support