Counting records by metric

Hi Jay,

Thanks for reaching out!

I appreciate the thorough explanation and the screenshot. I think I understand what you are looking to achieve here. Basically, you want to have a Pie/Bar chart showing the total number of injuries (each one defined by Injury ID), viewed by the Length of Service.

In this case, I think the solution would be to create a Pie/Bar chart where the Metric is the COUNT(Injury ID), Viewed by Length of Service. The results should be the count of injury per Length bucket.

Please give that a shot and let me know if it works out!

Best,

Francisco

Thanks for the extra info! Indeed, that would not work out.

I gave it some thought, and here’s the idea I came up with:

Create a separate metric counting the Incident ID for each result or range of results from the Length of Service metric. Something like this:

SELECT COUNT (Incident ID) WHERE (SELECT Length of Service by Bucket BY Incident ID) = 1 

or
SELECT COUNT (Incident ID) WHERE (SELECT Length of Service by Bucket BY Incident ID) BETWEEN 5 AND 10

You’d end up with 4/5 such metrics (which is the downside of this method), one for each result range (defined by the WHERE conditional). Then, add all of them to the Metrics section of your Pie chart. I tested it on my end, and it should get you the results you are looking for!

Let me know if that one works out better!

Francisco

Unfortunately, that solution does not work for me. I think the issue lies in mutilple date dimensions in play: Date (Event) and Date (Date of Hire). If I’m correct, we’ll have to rethink our data model accordingly to turn the length-of-service bucket into an attribute.