Creating Your Own Metrics with Formulas
Zoho Analytics with a powerful formula engine allows you to derive new business metrics from your data . This allows you to perform complex analysis and get the required insights from your data set
Zoho Analytics provides a set of in-built functions for well-known specific calculations. You can simply apply these functions over your data or create your own formula expression with thesefunctions for complex computations..
Zoho Analytics offers 3 types of formulas that you can use to define your metrics:
Formula Column
Aggregrate Formula
Report Formula
Formula Column
Formula column allows you to create a new computed column in your data table based on a formula expression that you provide.
Zoho Analytics provides a wide range of Logical, Statistical, Date and String functions. You can combine these functions along with arithmetic operators like +, -, / and * to formulate the formula column. The result of the calculation will be saved as a new column in your data table.
Example: The below is an example of Grading the sales based on sales amount using a Nested If formula. The grading derived are Excellent, Very Good, Good, Poort and Others.
if("Amount" >= 0 and "Amount" <= 500, 'Poor', if("Amount" >500 and "Amount" <= 15000, 'Good', if("Amount" > 15000 and "Amount" <= 30000, 'Very Good', if("Amount" > 30000, 'Excellent', 'Others'))))
The defined formula columns can be used in any report creation similar to other columns in the data table.
Click here to know more about Formula Column.
Aggregate Formula
Aggregate Formula allows you to create powerful metrics to address your business needs. These metrics often serve as the Key business performance indicators in your analysis & reporting. .
Using this you can perform advanced aggregate computations to derive a numeric value from other columns in your data. The result of the Aggregate Formula is not a single value. This will be dynamically computed for each data point in the report in which it is used.
Example: Let's take a delivery service as an example. How many of your delivery were on time and how many were delayed. You can check the delayed with the simple formula below. This calculates the time difference between arrival and delivery in hour and returns the count of deliveries which has exceeded the specified time.
countif(dateandtimediff(HOUR,"Package Delivery"."Arrival Time","Package Delivery"."Dispatched Time") > 24)
Click here to know more about Aggregate Formula.
Report Formula
Report Formula is an aggregate formula specificically created within a report. . Report Formula allows you to use the basic arithmetic operators such as +, -, * and / with a nested IF condition, over the columns used in the report. The Report formula will be added as a metric in the report.
https://show.zohopublic.com/publish/cyr6nf95b2bf69500456e9747eb22d5c616db/params?toolbar=true&menu=false&loop=true
Click here to know more about Report Formula.