Help desk managers are often tasked with gathering vast amounts of data to create metric-specific reports or dashboards such as average cost per ticket, service groups with maximum backlogs, areas with highest customer satisfaction, percentage of requests resolved within SLAs, most valuable technicians etc.,
Sometimes, the metric or KPI you need isn't readily available in your application. You need to either compute data from the raw data you get from your applications, or combine data from different sources and then apply statistical formulas to get the insights you need. Thankfully, we have you covered. Using Analytics Plus, you can easily run custom calculations to perform powerful calculations and gain deeper insights into your data.
Let's now delve into details of how to create calculated fields. There are two types of calculated fields you can create.
1. Custom formulas - This is useful when you wish to calculate individual values for each row in your data table.
Example - Calculating the time difference between the time when a request was created and when it was resolved. This will give individual resolution time values for each ticket in your data table.
2. Aggregate formula - This is useful when you wish to calculate a single overall value for your entire dataset.
Example - Calculating the average resolution time of all requests in your help desk. This calculated field gives a single overall value for all requests in your helpdesk.
Custom formula
Custom formulas include simple calculations such as addition or subtraction. Custom formulas are also useful for running string operations (typically useful in cleaning up the data). This can be achieved using in-built functions in your formula.
Click here
for a complete list of in-built functions. These in-built functions can be used in combination with basic arithmetic operations like sum, difference, multiplication or division.
A point to note while using custom formula is that the output of the formula adds a new column to your table that can be used to create reports and dashboards similar to any other column in the table.
To give you an idea of how custom formulas work, here are a few examples.
Name
|
Explanation
|
Formula
|
Days since created
|
Days since the request was created. This formula calculates the time difference between the created time and the current time
|
datediff(curdate(),"Created Time")
|
Escalated
|
Signifies the escalation status of the request. The formula returns the value 1 if the request was escalated and value 0 if it isn't.
|
if("IsEscalated", 1, 0)
|
Time to breach
|
Tells you the time remaining before a request violates SLA. It calculates the time difference between the current time and the time when the request is due for completion.
|
if("Request Pending Status"=true,dateandtimediff(MINUTE, currentdate(), "DueBy Time"),NULL)Here's a sample report created using custom formula, "Escalated":
|
Here's a sample report created using custom formula, "Escalated":
Aggregate formula
Aggregate formulas include functions such as sum, median, mode, mean, variance, etc., Similar to custom formulas, aggregate formulas can also be used in combination with in-built aggregate functions. However, unlike custom formulas, the result of an aggregate formula is always a numerical value (string outputs are not supported). The result of aggregate formulas are not displayed in your table. The result is listed in the left pane (for drag-n-drop) at the time of report creation.
Examples of aggregate formulas:
Name
|
Explanation
|
Formula
|
% of requests within SLA
|
Calculates the percentage of requests that have been resolved before violating the SLA
|
(("Request"."Requests within SLA"*1.0/count("Request"."RequestID"))*100)
|
Backlog request count
|
Count of requests that are still unresolved and overdue
|
Countif("Request"."Request Pending Status"='true' and "Request"."Overdue Status"='true')
|
% of escalated requests
|
Gives you the percentage of requests escalated to the next level
|
("Request"."Escalated Requests Count"/count("Request"."RequestID"))*100
|
Incident request count
|
Calculates the total number of incoming incident requests
|
countif("Request"."Service Request"='No')
|
% reopened
|
Percentage of requests reopened by users
|
("Request"."Reopen Requests Count"/count(distinct("Request"."RequestID")))*100
|
Here's a sample report created using these aggregate formulas:
The sample formulas given above should give you a basic idea of how custom and aggregate formulas work. If you wish to learn more about how to create custom and aggregate formulas, you can check this
help page
.
If you'd prefer a crash course to get you up to speed on using formulas and building reports/dashboards, sign up for a free
1-on-1 session
and our experts will guide you in building insightful reports and dashboards.
Want to learn at your own pace? Check out our
online demo
, and use the "Login as administrator" option to play around with our sample data set.
