Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I would like to count the number of tickets I have re-open this month.
In my scenario this is the status logic : Assigned - Work In progress - Cleared( from this stage we can move the ticket back to Assigned or Work In progress) - Closed.
I have two tables which one contain the current status of the tickets and one is the history status of the tickets.
Order:
LOAD * INLINE [
id, Status, OrderDate
1, Closed, 20/08/2018 08:00
2, Closed, 20/08/2018 08:00
3, Cleared, 20/08/2018 08:00
4, Assigned, 20/08/2018 09:00
5, Work In Progress, 20/08/2018 08:00
];
left join(Order)
StatusHistory:
LOAD * INLINE [
id, Status History, Timestamp
1, Assigned, 20/08/2018 08:00
1, Work In Progress, 20/08/2018 08:05
1, Cleared, 20/08/2018 08:55
1, Closed, 20/08/2018 09:20
2, Assigned, 20/08/2018 08:00
2, Work In Progress, 20/08/2018 08:05
2, Cleared, 20/08/2018 08:55
2, Work In Progress, 20/08/2018 09:20
2, Cleared, 20/08/2018 10:10
2, Closed, 20/08/2018 10:20
3, Assigned, 20/08/2018 08:00
3, Work In Progress, 20/08/2018 08:05
3, Cleared, 20/08/2018 08:55
3, Closed, 20/08/2018 09:20
4, Assigned, 20/08/2018 09:00
5, Assigned, 20/08/2018 08:00
5, Work In Progress, 20/08/2018 08:00
];
Could someone how count the number of re-open tickets?
Regards,
C
I would flag "re-opening" in the load script, and then sum up that flag:
StatusHistory:
LOAD * INLINE [
id, Status History, Timestamp
1, Assigned, 20/08/2018 08:00
1, Work In Progress, 20/08/2018 08:05
1, Cleared, 20/08/2018 08:55
1, Closed, 20/08/2018 09:20
2, Assigned, 20/08/2018 08:00
2, Work In Progress, 20/08/2018 08:05
2, Cleared, 20/08/2018 08:55
2, Work In Progress, 20/08/2018 09:20
2, Cleared, 20/08/2018 10:10
2, Closed, 20/08/2018 10:20
3, Assigned, 20/08/2018 08:00
3, Work In Progress, 20/08/2018 08:05
3, Cleared, 20/08/2018 08:55
3, Closed, 20/08/2018 09:20
4, Assigned, 20/08/2018 09:00
5, Assigned, 20/08/2018 08:00
5, Work In Progress, 20/08/2018 08:00
];
Left Join
LOAD *, If(id = Previous(id) and Previous([Status History]) = 'Cleared' and [Status History] <> 'Closed', 1, 0) as [Re-Opened]
Resident StatusHistory Order By id, Timestamp;
You can try counting everytime a ticket is more than once in status "Work in Progress".
count({<Status={"Work In Progress"}>} id) - count(Distinct {<Status={"Work In Progress"}>} id)
What is your expected output from your above sample data?
I would flag "re-opening" in the load script, and then sum up that flag:
StatusHistory:
LOAD * INLINE [
id, Status History, Timestamp
1, Assigned, 20/08/2018 08:00
1, Work In Progress, 20/08/2018 08:05
1, Cleared, 20/08/2018 08:55
1, Closed, 20/08/2018 09:20
2, Assigned, 20/08/2018 08:00
2, Work In Progress, 20/08/2018 08:05
2, Cleared, 20/08/2018 08:55
2, Work In Progress, 20/08/2018 09:20
2, Cleared, 20/08/2018 10:10
2, Closed, 20/08/2018 10:20
3, Assigned, 20/08/2018 08:00
3, Work In Progress, 20/08/2018 08:05
3, Cleared, 20/08/2018 08:55
3, Closed, 20/08/2018 09:20
4, Assigned, 20/08/2018 09:00
5, Assigned, 20/08/2018 08:00
5, Work In Progress, 20/08/2018 08:00
];
Left Join
LOAD *, If(id = Previous(id) and Previous([Status History]) = 'Cleared' and [Status History] <> 'Closed', 1, 0) as [Re-Opened]
Resident StatusHistory Order By id, Timestamp;
Thanks much appreciate!