This if my first post so apologies for any newbie errors....(!)
I've been working with QlikSense for a number of weeks now. I'm not a programmer or a DB admin at all but I've managed to blunder my way through and have started to create some really useful dashboards based on our PostgreSQL powered ticketing system (Web Help Desk by Solarwinds).
Working for a Managed Service Provider specialising in network infrastructure, I'm really interested in being able to display a bar graph telling be how long our carriers are spending fixing problems. The way that I want to do this is my using my ticketing system, more importantly the ticket status.
I have a number of statuses my tickets can be in e.g
I want to represent how much time each ticket has a status of "In progress with supplier" in a bar graph - something like this:
My database has a table called 'history_entry' which holds all of the status changes in the life of a ticket (plus other stuff). The 'job_ticket_id' is the ticket number:
|15151||2015-09-24 17:30:01||Carrier/Supplier changed from N/A to Ingenitec|
|15156||2015-09-29 17:04:30||Carrier/Supplier changed from None to Vodafone|
|15160||2015-10-01 17:19:11||Carrier/Supplier changed from None to N/A|
|15162||2015-10-02 16:27:53||Carrier/Supplier changed from N/A to Vodafone|
|15171||2015-10-11 12:28:46||Status changed from In Progress With Supplier to Resolved - Pending Closure|
|15171||2015-10-10 02:43:40||Status changed from Open to In Progress With Supplier|
|15176||2015-10-21 16:19:13||Carrier/Supplier changed from None to N/A|
|15180||2015-10-28 13:26:07||Carrier/Supplier changed from None to N/A|
|15187||2015-11-02 11:01:14||Status changed from In Progress With Supplier to Closed|
|15187||2015-10-30 13:28:54||Status changed from Open to In Progress With Supplier|
So for example, in the case of ticket reference 15171 above, the ticket transitions into a status of "In progress with supplier" at 02:43:40 on 10/10, and then moves into a status of "Resolved - pending closure" at 12:28:46 on 11/10. I would therefore want to represent the total time spent in this status in hours (roughly 33 hours in this case). The average this out across all tickets and represent the data per carrier as in the graph above.
If anyone can let me know if the above is possible and if so point me in the right direction I would really appreciate it! If you need any more then please just let me know!