Re-open ticket

[0li5a3a]

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

[maren_amthor]

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)

[vishsaggi]

What is your expected output from your above sample data?

[andrey_krylov · Accepted solution]

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;

[0li5a3a]

Thanks much appreciate!