Solved: How can I calculate total work time and time spent... - Qlik Community

alespooletto · ‎2025-02-10

I have a table in QlikView that tracks ticket history, and I need to calculate two things for each ticket: the Total work time and the Time spent in DE: I need to track the time when the ticket was in status Z28C (which represents the DE stage), and the time spent there.

Here's the structure of my data:

Ticket History Table:

HIST_TicketID	HIST_TicketRow	HIST_Date	HIST_Workhourminutes	HIST_RowTypeID	HIST_RowType	HIST_FromID	HIST_ToID
959C	1	2025-02-06 16:07:04.673	0	0	Created	NULL	S1
959C	2	2025-02-06 16:07:31.647	1	3	Forwarded	G7C	P15C
959C	3	2025-02-06 16:07:36.663	1	20	Status changed	S1	S2
959C	4	2025-02-06 16:07:54.953	1	23	Category changed	NULL	NULL
959C	5	2025-02-06 16:09:01.887	2	20	Status changed	S2	Z28C
959C	6	2025-02-07 09:50:24.023	1062	20	Status changed	Z28C	S2
959C	7	2025-02-07 09:51:00.683	1063	20	Status changed	S2	Z18C

The statuses:

STAT_ID	STAT_Name	STAT_Type
S1	Open	Open
S2	In progress	Open
S3	Request Info	Suspended
S4	Refused	Closed
S5	Closed	Closed
Z18C	Solved	Suspended
Z28C	Forwarded to DE	Open

I want to calculate:

The total time worked by my team (Time_Worked_By_Team), which is the total time minus the time spent in DE.
The time spent in DE (Time_In_DE), which should be tracked while the ticket is in status Z28C.

Here is what I need to do:

Identify when the ticket enters DE (status Z28C) and when it leaves.
Sum the work time for each ticket, but exclude the time spent in DE.
I also need to keep both the total work time and the time spent in DE in the final result.

I’ve attempted a QlikView script like this, which would work before this DE forwarding got incorporated

MaxTicketRow:
LOAD
Ticket_ID,
MAX(Riga_Ticket) AS MaxTicketRow
RESIDENT TicketsHistory
GROUP BY Ticket_ID;

LEFT JOIN (TicketsHistory)
LOAD
Ticket_ID,
MaxTicketRow
RESIDENT MaxTicketRow;

TicketsHistoryMax:
LOAD *,
IF(Riga_Ticket = MaxTicketRow, HIST_Workhourminutes) AS Durata_Totale,
IF(Riga_Ticket = MaxTicketRow, HIST_Workhourminutes / 60) AS Durata_Ore
RESIDENT TicketsHistory;

DROP TABLE TicketsHistory;
DROP TABLE MaxTicketRow;

This works for the most part, but I need to ensure that I handle the time spent in DE correctly, especially when it’s split across multiple status changes.

How can I correctly calculate both total work time and time spent in DE, while maintaining the correct structure and handling transitions between statuses in QlikView?
Do I need to account for any additional edge cases, like multiple transitions to DE or splitting time across different statuses?

Any help or insights would be greatly appreciated

Qrishna · ‎2025-02-14

Here we go:

//Load Data From Source
WebData:
LOAD *
FROM
[https://community.qlik.com/t5/App-Development/How-can-I-calculate-total-work-time-and-time-spent-in-...]
(html, codepage is 1252, embedded labels, table is @1);

//Make Sure you order by Ticket, Row or the key/index as we use Previous() fucntion here
TicketHistory:
LOAD *,
if(HIST_TicketID = PREVIOUS(HIST_TicketID) AND
HIST_FromID = PREVIOUS(HIST_ToID) AND
HIST_FromID = 'Z28C'
, PREVIOUS(HIST_Workhourminutes) , 0) AS TotalWorkTime_Less_DE_Temp;
LOAD HIST_TicketID,
HIST_TicketRow,
HIST_Date,
HIST_Workhourminutes,
HIST_RowTypeID,
HIST_RowType,
HIST_FromID,
HIST_ToID
RESIDENT WebData
ORDER BY HIST_TicketID, HIST_TicketRow, HIST_Date;
DROP TABLE WebData;

//Extract the Masx Ticket Rowno for a ticket
LEFT JOIN(TicketHistory)
MaxTicketRow:
LOAD HIST_TicketID ,
MAX(HIST_TicketRow) AS MaxTicketRow
RESIDENT TicketHistory
GROUP BY HIST_TicketID;

//Extract Total work time in DE
LEFT JOIN(TicketHistory)
TotalTimeWorked:
LOAD HIST_TicketID ,
HIST_Workhourminutes AS TotalWorkTime_with_DE
RESIDENT TicketHistory
Where HIST_TicketRow = MaxTicketRow;

//Extract DE Enter Time
LEFT JOIN(TicketHistory)
Time_In_DE:
LOAD HIST_TicketID ,
HIST_Date AS DE_Enter_Time
RESIDENT TicketHistory
Where HIST_ToID = 'Z28C';

//Extract DE Exit Time and total time spent in DE
LEFT JOIN(TicketHistory)
Time_In_DE:
LOAD HIST_TicketID ,
HIST_Date AS DE_Exit_Time,
HIST_Workhourminutes - TotalWorkTime_Less_DE_Temp AS Time_In_DE
RESIDENT TicketHistory
Where HIST_FromID = 'Z28C';

RENAME TABLE TicketHistory TO Temp;

TicketHistory:
LOAD *,
TotalWorkTime_with_DE - Time_In_DE AS TotalWorkTime_Less_DE // Total Work Time less DE time
RESIDENT Temp;
DROP TABLE Temp;

DROP FIELDS
MaxTicketRow,
TotalWorkTime_Less_DE_Temp;

Alternatively, Time_In_DE can also be calculated as DE_Exit_Time - DE_Enter_Time, As this is already calcuated in your table, we can directly use that number.

View solution in original post

Chanty4u · ‎2025-02-11

You can modify script

TicketsHistoryMax:

LOAD *,

If(Riga_Ticket = MaxTicketRow, HIST_Workhourminutes) AS Durata_Totale,

If(Riga_Ticket = MaxTicketRow, HIST_Workhourminutes / 60) AS Durata_Ore,

If(HIST_Status = 'DE', Interval(HIST_Timestamp - Peek('HIST_Timestamp'), 'hh:mm:ss')) AS TimeIn_DE

RESIDENT TicketsHistory

ORDER BY Riga_Ticket, HIST_Timestamp;

DROP TABLE TicketsHistory;

DROP TABLE MaxTicketRow;

Qrishna · ‎2025-02-12

try Below:

TicketHistory:
LOAD Recno() as sno,
HIST_TicketID,
HIST_TicketRow,
HIST_Date,
HIST_Workhourminutes,
HIST_RowTypeID,
HIST_RowType,
HIST_FromID,
HIST_ToID
FROM
[https://community.qlik.com/t5/App-Development/How-can-I-calculate-total-work-time-and-time-spent-in-...]
(html, codepage is 1252, embedded labels, table is @1);

Left Join(TicketHistory)
MaxTicketRow:
LOAD HIST_TicketID ,
MAX(HIST_TicketRow) AS MaxTicketRow
RESIDENT TicketHistory
GROUP BY HIST_TicketID;

Left Join(TicketHistory)
TotalTimeWorked:
LOAD HIST_TicketID ,
HIST_Workhourminutes AS TotalWorkTime_with_DE
RESIDENT TicketHistory
Where HIST_TicketRow = MaxTicketRow;

Left Join(TicketHistory)
Time_In_DE:
LOAD HIST_TicketID ,
HIST_Date AS DE_Enter_Time
RESIDENT TicketHistory
Where HIST_ToID = 'Z28C';

Left Join(TicketHistory)
Time_In_DE:
LOAD HIST_TicketID ,
HIST_Date AS DE_Exit_Time ,
HIST_Workhourminutes AS Time_In_DE
RESIDENT TicketHistory
Where HIST_FromID = 'Z28C';

Rename Table TicketHistory To Temp;

TicketHistory:
Load *,
TotalWorkTime_with_DE - Time_In_DE As TotalWorkTime_Less_DE
Resident Temp;
Drop Table Temp;

Drop Fields
MaxTicketRow;

alespooletto · ‎2025-02-13

Hello @Chanty4u thank you for your answer. Could you please tell me where you got the HIST_Timestamp field from?

alespooletto · ‎2025-02-13

Thanks @Qrishna for the complete answer. Truly, this is almost complete, and probably I can work on the next stuff by myself, the only thing missing here is that the final result should not be 1 but 3, because removing the time in DE also removes the time that was spent before. So the 2 minutes it had before going to DE should still be counted.

Thanks again though!

Qrishna · ‎2025-02-14