topic Subtract values for time between 6 pm and 8 am everyday in App Development

Subtract values for time between 6 pm and 8 am everyday

alespooletto — Mon, 02 Dec 2024 11:14:35 GMT

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

Re: Subtract values for time between 6 pm and 8 am everyday

QFabian — Wed, 04 Dec 2024 18:27:40 GMT

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

Re: Subtract values for time between 6 pm and 8 am everyday

QFabian — Wed, 04 Dec 2024 19:52:24 GMT

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));

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

check the atached QVF with the entire exercise