How to calculate working time on week level

Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I have this table:

and i need to display WorkingTime, on week level, something like this:

In this example i used round 90 Working hours.

I calculate minutes to find out working hours like this :

Let vStart = floor(40179);

Let vEnd = floor(now());

TempCalendar:

LOAD

DataChange,

working_hour,

IF(isnull(peek(working_hour)), 0, peek('minute') + working_hour) AS minute;

LOAD

text(timestamp(Data,'YYYY-MM-DD hh:mm')) as DataChange,

IF(match(floor(WeekDay(Data)), 5, 6), 0,

IF((hour(Data) >= 9 and hour(Data) < 18), 1, 0)

) AS working_hour;

LOAD

$(vStart) + (IterNo()-1)/24/60 As Data,

rowno()

AutoGenerate 1 While $(vStart) + (IterNo()-1)/24/60 <= $(vEnd);

left join (time2)

LOAD

DataChange AS DataChange_start,

minute as minute_start

RESIDENT TempCalendar;

left join (time2)

LOAD

DataChange AS DataChange_end,

minute as minute_end

RESIDENT TempCalendar;

Can anyone give me an idea on how can i solve this please ?

Thank you,

topic How to calculate working time on week level in QlikView