Jump to content
Guest Sonali

Open incidents by age

Recommended Posts

Guest Sonali

Hi 

I want to create a pie chart showing the % of open incidents by age (seven or less days open; greater than 7 but less than 30 days and greater than 30 days)

I am not sure how to go about creating this. 

Please can you help me

Sonali

Share this post


Link to post
Share on other sites
Guest Sonali

Yes this is what I am looking for. I can't see the logic behind to how it was created on the thread

Share this post


Link to post
Share on other sites

@Sonali is a bit difficult/complex to explain in writing. Perhaps if I create a test widget in your instance which we can discuss it after?

Share this post


Link to post
Share on other sites
Guest Sonali

Hi

Yeah that would be great

Sonali

Share this post


Link to post
Share on other sites

@Sonali I had a play around with widgets and measures and the best what I can come up with is a widget as per below (this is from your instance). I can't do a (pie) chart type because I can't use current pie chart functionality to get the data I need to do all the % grouping and calculations... besides, pie chart requires measures and measures can't give you current period data...

Open_Incidents_Age.PNG

Share this post


Link to post
Share on other sites

For anyone interested, the above widget can be created using a custom SQL query like this one:

SELECT 
    ROUND(SUM(CASE
        WHEN DATEDIFF(NOW(), h_datelogged) <= 7 THEN 1
        ELSE 0
    END)*100/COUNT(*),0) AS '0-7 Days (%)',
    ROUND(SUM(CASE
        WHEN DATEDIFF(NOW(), h_datelogged) BETWEEN 8 AND 29 THEN 1
        ELSE 0
    END)*100/COUNT(*),0) AS '8-29 Days (%)',
    ROUND(SUM(CASE
       WHEN DATEDIFF(NOW(), h_datelogged) >= 30 THEN 1
        ELSE 0
    END)*100/COUNT(*),0) AS '30+ Days (%)'
FROM h_itsm_requests
WHERE h_status IN ('status.open', 'status.onhold', 'status.resolved')

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...