Subtract values for time between 6 pm and 8 am everyday

[alespooletto]

Hello, I have a script which loads a simple table with data on help desk tickets.

In this table, there's a column with data corresponding to the duration of a ticket, calculated from the creation date, to the last update for each row. Accordingly, I only want to get information for when the ticket is closed entirely, so I get the final duration. However, there is an issue, for example if the ticket is opened at 4pm, and closed the second day at 10am, the duration will take into account also the time after 6pm and up to 8am. But the helpdesk only works within 8am-6pm, so I shouldn't calculate that. 

How can I fix this by using qlik load editro script? Here is what I am loading rn

LOAD HIST_ID, 
	HIST_TicketID as TKT_ID, 
	HIST_TicketRow, 
    HIST_Date, 
	HIST_RowTypeID, 
	HIST_RowType, 
	HIST_FromID, 
	HIST_ToID, 
	HIST_ResourceID, 
	HIST_AccountID, 
	HIST_ContactID, 
	HIST_TicketTypeID, 
	HIST_TicketStatusID, 
	HIST_TicketPriorityID, 
	HIST_TicketUrgencyID, 
	HIST_TicketGroupID, 
	HIST_TicketResourceID, 
	HIST_AdditionalInfo
    HIST_Workhourminutes;
    
[TicketsHistory]:
SELECT "HIST_ID",
	"HIST_TicketID",
	"HIST_TicketRow",
    "HIST_Date",
	"HIST_RowTypeID",
	"HIST_RowType",
	"HIST_FromID",
	"HIST_ToID",
	"HIST_ResourceID",
	"HIST_AccountID",
	"HIST_ContactID",
	"HIST_TicketTypeID",
	"HIST_TicketStatusID",
	"HIST_TicketPriorityID",
	"HIST_TicketUrgencyID",
	"HIST_TicketGroupID",
	"HIST_TicketResourceID",
	"HIST_AdditionalInfo",
    "HIST_Workhourminutes"
FROM TicketsHistory
WHERE [HIST_TicketGroupID] = 'G7C';

MaxTicketRow:
LOAD
    TKT_ID,
    MAX(HIST_TicketRow) AS MaxTicketRow
RESIDENT TicketsHistory
GROUP BY TKT_ID;

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

TicketsHistoryMax:
LOAD *,
    IF(HIST_TicketRow = MaxTicketRow, HIST_Workhourminutes, 0) AS Final_Workhours
RESIDENT TicketsHistory;

DROP TABLE TicketsHistory;
DROP TABLE MaxTicketRow;

So ideally, this end up like this: 

TKT_ID	HIST_Date	HIST_Workhourminutes	Final_Workhours
103C	2024-10-13 16:00:30.690	0	0
103C	2024-10-14 10:00:30.690	1080	240

This is a simplistic example. The Final_Workhours is 240 because its only the time passed between 4pm and 6pm and then 8am to 10am

[QFabian]

Hi @alespooletto ,i found these inside Qlik Community :

https://community.qlik.com/t5/QlikView-App-Dev/Duration-Business-hours/td-p/1241438

https://community.qlik.com/t5/QlikView-App-Dev/Working-hours-only/td-p/388475

[QFabian · Accepted solution]

Hi @alespooletto , try this example, it just generate the valid working hours to sum them

Aux:

Load * INLINE [

Ticket, Start, End

103C, 2024-10-13 16:00:30.690, 2024-10-14 10:00:30.690

];

Let vRows = NoOfRows('Aux') - 1;

For vRow = 0 to $(vRows)

Let vTicket = peek('Ticket', $(vRow), 'Aux');

    Let vStartHour = hour((peek('End', $(vRow), 'Aux');

Let vHours = ceil((peek('End', $(vRow), 'Aux') - peek('Start', $(vRow), 'Aux')) * 24);

Data:

Load distinct

'$(vTicket)' as Ticket, 

        timestamp(peek('Start', $(vRow), 'Aux') + ((1/24) * (recno()-1)), 'DD-MM-YYYY hh:mm') as DateTime,

subfield(hour(peek('Start', $(vRow), 'Aux') + ((1/24) * (recno()-1))), ':', 1) as HourIndex,

        if((subfield(hour(peek('Start', $(vRow), 'Aux') + ((1/24) * (recno()-1))), ':', 1) >= 8 and

      subfield(hour(peek('Start', $(vRow), 'Aux') + ((1/24) * (recno()-1))), ':', 1) <= 18)

, 1) as HourSum,

if((subfield(hour(peek('Start', $(vRow), 'Aux') + ((1/24) * (recno()-1))), ':', 1) >= 8 and

      subfield(hour(peek('Start', $(vRow), 'Aux') + ((1/24) * (recno()-1))), ':', 1) <= 18)

, 'V') as Type

AutoGenerate($(vHours));

Next

It adds every hour classifying it according to defined working hours :

check the atached QVF with the entire exercise