Result metric calculations enable you to add a further calculation to a query you have already created. For example, if you've created a query to calculate a cost of service for your tickets (see Calculating estimated cost of service for an example), and you want to add a ten percent margin to this, you could use a result metric calculation.
Creating a result metric calculation
To create a result metric calculation
- With an Explore query open, click the result manipulation menu (
), then select Result metric calculation. - Click Add a new metric.
- On the Result metric calculation page, enter the name of your metric.
- Enter your formula. For help writing formulas, see Writing Explore formulas.
- Select Clear used metrics to hide other metrics from the query results and only show the result metric calculation. The metrics are still used in the result metric calculation, but the results will not appear in your chart.
- Click Add, then close the Result metric calculation page. The new result metric calculation is added to the query.
- Your calculation is automatically added to the open query.
- In your query, click Save.
Example: Calculating the percentage of on-hold tickets
In this example, you'll create a query that shows the total number of tickets, and the number of on-hold tickets for any given year. Then, you'll add a result metric calculation that displays the percentage of on-hold tickets.
Creating the query
To create the query
- In Explore, create a new query using the Support: Tickets dataset.
- In the Metrics panel, add the following metrics:
- COUNT(Tickets)
- COUNT(On-hold tickets)
- In the Filters panel, add the following attribute:
- Ticket created - Year
- From the visualization type menu (
), choose Table.
Explore displays a table showing the total number of tickets and the number of on-hold tickets. You can click the Ticket created - Year filter to filter your results to specific years.
- Enter a name for the query, then click Save.
Adding the result metric calculation
To add the result metric calculation
- With an Explore query open, click the result manipulation menu (), then select Result metric calculation.
- Click Add a new metric.
- On the Result metric calculation page, enter the name of your metric, Percentage of on-hold tickets.
- Enter the following formula:
COUNT(On-hold tickets)/COUNT(Tickets)The Result metric calculation page will look like the following example:
- Click Add, then close the Result metric calculation page. The new result metric calculation is added to the query.
- Next, you need to display the new calculation as a percentage. In the chart configuration menu (
), click Display format. - On the Display format page, click the drop-down list next to your metric, SUM(Percentage of on-hold tickets).
- From the list of display formats, choose percentage, (%). The result metric calculation in your query now displays a percentage.
- In your query, click Save.
15 Comments
Hi Rob,
What if I wanted to compare the total tickets from last month versus the total tickets from this month to provide a percentage of how this month's ticket volume compares to last month's ticket volume. Then the end result would be a KPI displaying the percentage that this month's ticket volume would be over or under last month's ticket volume. If it were over there would be a green up arrow pointing up or the text would be green. If it were under, then there would be a red arrow pointing down or the text would be red.
Insights does this, but I haven't figured out how to get Explore to accomplish this. Thank you.
Hi Eric, here's how I managed to do this.
1. Create two Date range calculated metrics:
Tickets this month (Original metric: Tickets, Defined on: Ticket created, Date range: This month)
Tickets last month (Original metric: Tickets, Defined on: Ticket created, Date range: last month)
2. Add both of these metrics to the metrics panel. Tickets this month should be the top-most metric.
3. In the chart configuration menu to the right, click Chart. In the Chart menu, enable "Show variation" and set "Variation type" to Percentage.
That gave me this:
I hope this helps, or gives you some pointers to get started!
Rob Stack would you be willing to share the calculated metric you created?
Rob Stack I do not want the "24 804" to display on the stat. Can you explain how I'd get rid of this? Thank you.
Also, I'm not seeing the "Show variation" or the "Variation type" fields. See below. Thoughts?
Bobby Koch It's actually two date range calculated metrics. You'll find that option just below the standard calculated metric. They should be set up with the following settings:
Name: Tickets this month (Original metric: Tickets, Defined on: Ticket created, Date range: This month)
Name: Tickets last month (Original metric: Tickets, Defined on: Ticket created, Date range: last month)
Eric Haley the Chart menu differs depending on which chart type you select. It looks like in your screenshot the Table type is selected. Could you try again with the KPI chart type selected.
Sorry, but I don't see a way to remove the other number. You can make it smaller (Value style) or change its' color (Value style). One thing I guess you could do is to make that number the same color as your background, thus making it invisible. That's not perfect when you add it to a dashboard but with some resizing you can make it look quite good.
Thanks, Rob Stack. That was it. Now, unfortunately, I discovered a new problem. I have a time filter on my report. When I change the month of the report, the stat drops to 0 tickets (down by 100%). I think this is because the "Tickets Created - This Month" and "Tickets Created - Last Month" metrics are static; thus always referring to the current and the previous month, regardless of what the time filter states. Is there are a way to do this so that the stat comes out dynamic, switching to the time frame that the time filter stipulates? Thanks again.
Eric Haley If you've added your percentage change query to the dashboard, you could exclude it from the time filter:
Hello,
I am trying to show the percentage of each category but the formula text box border is red and I cant figure out what am I doing wrong.
Right now this query is shared in different Dashboards and I would like to add a column that shows the %.
What formula should I be using in order to do so? If I go to display options and select % I get this.
Any help would be appreciated.
Hi Jorge Marques could you share the formula (that's showing red)? Happy to take a look at this!
Hiya all
I am also suffering from not being able to work on my metrics. I have added my screenshot. What I want it to do is show the percentage of items reported/removed over the years and this to be shown in a line graph. I have tried creating a new attribute (so it is a metric) and using the result metric calculation - neither of these is work for me. Can you help?
Hi Zara Roddis thanks for the question! It looks like you're using custom metrics here. Would you mind sharing the formulas you used, then we can take a look. Thanks!
Is there a way to do a result metric calculation on a result metric calculation?
I am looking at grouping MED(First Reply Time (hrs)) into 4 buckets. I would then like to get a count of how many times each bucket shows up as a percentage of the whole. My chart looks like this:
"Traffic Light" is a metric calculation that uses an IF/THEN formula to group Reply Times into buckets. (eg. IF [time] > 1 THEN 4 ELIF...) etc.
So now I want to know what percentage of cells (in the whole table) are a 4, a 3, a 2, and a 1. And preferably, only display that.
Hello Geoffrey Gallinger,
The only way to accomplish this would be to create your initial result metric calc as a custom metric instead and then use that in a result metric calculation. Otherwise, there is no native way to accomplish this at this time.
Best regards.
Please sign in to leave a comment.