Hello
I hope someone can help me with a query on QlikView.
We are using ServiceNow to log our tasks and incidents at work.
I am trying to create a dashboard showing when a call was responded and fixed and if it was within SLA.
From my attachment, the first screenshot, you ca see that an Incident has and Assignment group and Assigned to.
I have successfully created the logic to determine when the Incident metric has been allocated to the Incident Assignment group
Load *,
If([Incident Metric Definition])='Assignment Group' and [Incident Metric Definition] = 'Assigned to Duration' and [Incident Metric Value]=([Assignment group]),mi_start,null()) as Official_Start_Time
;
What I need help with is to determine the first time a member (Assigned to) of that Assignment group responded to that Incident.
On the second screenshot of the attachment you can see we have 2 [Incident Metric Value], Paul McGinnis and Andrew Boyd. Paul McGinnis and Andrew Boyd are both members (Assigned to) of the Assignment group OPS-Windows-Server. But Paul McGinnis responded to the call first at 2016-07-12 13:40:53, but as he is also a member of the Assignment group PS-TCS-Print my logic is not picking his start time up, but it is picking up 2016-07-14 12:51:02 for [Incident Metric Value] Andrew Boyd. Please see my logic below.
Does anyone know of a way that I can use the above logic for the Official_Start_Time to also find the first (Assigned to) of the Assignment group OPS-Windows-Server. To give me Paul McGinnis response time of 2016-07-12 13:40:53.
Any help would be appreciated. I hope this makes sense to someone, I think I have waffled on a bit
Thank you
Stephen
NoConcatenate
Incident_touched_by:
Load *,
If(previous([Incident Metric Definition])='Assignment Group' and [Incident Metric Definition] = 'Assigned to Duration' and Previous([Incident Metric Value])=Previous([Assignment group]),mi_start,null()) as Official_Start_Time
;
LOAD
*
Resident incident_metric
where [Incident Metric Value] <> ''
and Match([Incident Metric Definition],'Assignment Group','Assigned to Duration')
order by Incident.Number asc, mi_start asc, [Incident Metric Definition] asc;
//order by Incident.Number asc, mi_start asc, [Incident Metric Definition] desc;
First_Touched_Map:
Mapping Load Incident.Number,
Official_Start_Time
Resident Incident_touched_by
where not IsNull(Official_Start_Time);
Drop Table Incident_touched_by;
min_start_temp:
Load *,
Interval(First_Touched_Time-[First Repsonse Contact],'dd:hh:mm:ss') as "SLA L2 Response",
Load *,
ApplyMap('First_Touched_Map',Incident.Number,'No mi_start map') as First_Touched_Time
Resident min_start;
DROP Table min_start;
RENAME Table min_start_temp to min_start;
