Insights metrics - not working?
回答済みHello team,
I have added a range of 'insight' metrics (it doesnt give an example of what a metric will return - making the process far too drawn out)
Anyway - I finally have metrics a,b and c... all of which count tickets by the number of certain tags present.
Then I have another metric that ADDs metric a,b, and c (using SELECT a + b + c), this fails to show a result.
Creating custom metrics is so frustrating - but I guess I'll learn.
-
Andrew
Welcome back! We've missed you :)
You can add two custom metrics together in a new metric to calculate a total, but for tags, you may not want to.
Say you have a ticket with two tags (tag1 and tag2) . Your count of tag1 will be 1 and your count of tag2 will also be 1, so add them together and you get 1+1=2. So for one ticket, your combined count metric returns the value 2. I am not sure if that is want you are intending to see?
To create a metric that counts the number of tickets with a specified tag, I would clone the #Tickets metric to read:
#Tickets with tag1:
SELECT COUNT(Ticket Id,TicketTagId) where Ticket Status<>Deleted AND Ticket Tag Deleted Flag <> true and Ticket Tag=tag1
#Tickets with tag2:
SELECT COUNT(Ticket Id,TicketTagId) where Ticket Status<>Deleted AND Ticket Tag Deleted Flag <> true and Ticket Tag=tag2
You can then create your sum metric to add the two together in a new metric.
SELECT #Tickets with tag1 +#Tickets with tag2
it may be better to create a new metric that tests for both tags.
SELECT COUNT(Ticket Id,TicketTagId) where Ticket Status<>Deleted AND Ticket Tag Deleted Flag <> true and Ticket Tag in (tag1,tag2)
and this will avoid one ticket returning the value 2 if both tags are present.
Hope that helps.
-
Tags are exclusive - will never be both tags... therefore should my system work?
Ok - I have this...
Metric1 - "SELECT # Tickets WHERE Ticket Tag= tag1" [this returns 34]
Metric2 - "SELECT # Tickets WHERE Ticket Tag= tag2" [this returns 6]
Metric3 - "SELECT # Tickets WHERE Ticket Tag= tag3" [this returns nothing - not a 0, just blank]
Then I have...
Metric4 - "SELECT Metric1 + Metric2 + Metric3" [blank - no result? - expecting 40]
Next I want to create a percentage... metric1 / metric4 as a percentage.
Can you see where I am going wrong?
-
Andrew
Anything plus null is null. So you metric3 is returning null not zero and adding null to the other results produces null.
To correct this, modify your metric4
SELECT IFNULL(metric1,0) + IFNULL(metric2,0)+ IFNULL(metric3,0)
So, if any of your custom metrics return null, substitute zero and you should be fine.
ログインしてコメントを残してください。
3 コメント