Invalid expression while loading the data (calculation working hours between two dates)

[LK13]

Hello all,

I have a table with following fields:

TicketID, StartDate and EndDate. 

I have defined 2 variables: set vWorkDayStart = '08:00:00'; set vWorkDayEnd = '16:00:00';

I have following script:

mainTable:

Load

TicketID,

IF("New_Date" >= Floor("New_Date") + Time#('$(vWorkDayEnd)', 'hh:mm:ss'), 0,
Interval(Min("Response Date", Floor("New_Date") + Time#('$(vWorkDayEnd)', 'hh:mm:ss'))
- Max("New_Date", Floor("New_Date") + Time#('$(vWorkDayStart)', 'hh:mm:ss')), 'hh')) AS FirstDayHours,

IF(Floor("Response Date") > Floor("New_Date") + 1,
(Floor("Response Date") - Floor("New_Date") - 1) * 8, 0) AS FullDaysHours,

IF(Floor("Response Date") = Floor("New_Date"), 0,
Interval(Min("Response Date", Floor("Response Date") + Time#('$(vWorkDayEnd)', 'hh:mm:ss'))
- Floor("Response Date") - Time#('$(vWorkDayStart)', 'hh:mm:ss'), 'hh')) AS LastDayHours

Resident Table;

From some reason this is not working, it said that expression is invalid.

Do you have any insight on this ?

[marksouzacosta]

Hi @LK13,

You cannot use Min/Max and other aggregation functions without a Group By.

One recommendation: create the fields in advance instead of manipulating them inside expressions. This will help you to validate if the columns are getting the correct values.
Create fields for expressions like:
Floor("New_Date") + Time#('$(vWorkDayEnd)', 'hh:mm:ss')
Floor("Response Date")
Floor("New_Date") + 1
Floor("Response Date") - Floor("New_Date") - 1) * 8
Etc, etc

Regards,

Mark Costa

[LK13]

Hi Mark,

Thank you for your response.

I validate data type etc since not working to me. I also tried with RangeSum on front, not working. 

If you have any other way how to calculate working hours within defined time 08:00 - 16:00 from Start to End Datetime will be highly appreciated. Below is table representing what final result should be:

Start Time	End Time	Result (hh:mm:ss)
09/10/2024 11:22	09/10/2024 12:22	01:00:00
09/10/2024 16:22	09/10/2024 10:00	02:00:00

Timestamp(floor("New_Date")) as Floor_New_Date,
time('$(vworkdayend)') as WorkDayEnd,
time('$(vworkdaystart)') as WorkDayStart,
floor("New_Date") + time#('$(vworkdayend)', 'hh:mm:ss') as End_Of_Workday,
floor("New_Date") + time#('$(vworkdaystart)', 'hh:mm:ss') as Start_Of_Workday,
Timestamp(floor("Response Date")) as Floor_Response_Date,
floor("New_Date") + 1 as New_Date_One,
(floor("Response Date") - floor("New_Date") - 1) * 8 as Full_Days_Hours

[Kushal_Chawda · Accepted solution]

@LK13  refer to below post

https://community.qlik.com/t5/QlikView-Documents/Working-Hours-Calculation-excluding-weekends-and-ho...

[LK13]

Hi Kushal,

Thank you for your answer. I think I managed to have as expected as per few examples I checked, if there is something wrong i will come back.