In this tutorial we walk through some key features in Zoho Analytics; formula columns, aggregate formulas, and intro-level SQL. To learn more about Zoho Analytics you can check out our Full Product Tutorial.
Key Takeaways
Formula Columns
A formula column is going to run some type of calculation on every row individually. This is useful when needing to calculate averages, percentages, etc. Here’s a breakdown of how to create a formula column:
- On the top left of your data table in Zoho Analytics, click on Add then select the Formula Column option.
- A popup page on the right-hand side of the screen will appear, this is where you will name and create the formula.
- The two tabs in the Add Formula Column page are Columns, which are the data in the table. The other is Functions, which are what we can do with this data. There is a huge variety of functions that you can use to create the formula column, it all depends on what you are looking to calculate.
- When you hover over the function a popup will appear defining the function, stating the parameters, giving an example use case, an alternative function, and some additional notes.
- In this example, we are going to choose the “Days Between” function to calculate the Days to Convert a lead within our data table.
- After you choose the function that you will be using, flip back over to the Columns tab. Now select which columns to run the function on. In our example, we use the “Created Time” and “Converted Time”. Hit save once your formula is created.
- It may take a few minutes for the formula to be applied to the data table, depending on the number of rows it is computing.
- A new column will appear in the data table with your formula column results!
Aggregate Formulas
Aggregate formulas are a bit different, they are designed to run on more than one row at a time. When you add a formula column it’s going to run for every single row in the table. However, when you run an aggregate formula, it’s not going to add a column. The aggregate formula data will be in Reports.
Create the aggregate formula similar to the formula column by clicking Add then select the Aggregate Formula option. The same-looking popup on the right will appear, but now there is the additional Aggregate tab to choose from. Select the Aggregate function of your choice and create the formula. What you’ll notice after saving, there is not a new column created. The formula you created is going to run when we tell it to and display it based on the context of when we’re asking it to run.
In this example, we create a pivot table to display the aggregate formula that was created. If you are not familiar with creating pivot tables in Zoho Analytics, check out this section from our full product webinar. When putting together your pivot table you will see the new formula column that you previously created as well as the aggregate formula.
What you’ll notice is that yes Zoho Analytics is a very powerful tool, however, it might not give you every single number and value that you are looking for. Oftentimes it will be super useful to be able to use these formulas and aggregate formulas to slice and dice your data just as you need it.
Introduction to SQL
A lot of times you can pull reports based on one data source or on a collage of tables inside of a pivot view. Sometimes though, you might want to do some custom data management. In order to do so, you can open up the Query Table under the Create tab. Here is where you can build your own table. Add the different tables and columns to your query that you would like to have as columns under SELECT. Then you will add the FROM statement which tells the columns exactly where to get that data from. Find a common column across both of the data tables that you will be using. This column will be the one that you use to JOIN the two tables together.
There’s a lot you can do with SQL tables. The core idea is that you can create your own data table that is pulling values from a variety of different tables. Zoho Analytics formulas and SQL are a great way to pull specific data points that you wouldn’t find in the data table.
Additional Resources
Want to learn more about Zoho Analytics? Check out our other resources which include videos, articles, and guides by clicking here to visit the Zoho Analytics resource page.
