# 3 コメント

Community Moderator

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.

Community Moderator

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?

Community Moderator

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.

サインインしてコメントを残してください。