Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to calculate working time on week level

Hello,

I have this table:

JIDPKEYDataChange_startDataChange_endminute_startminute_endWorkingTime
2837562EOSMKP-110571/6/2016 10:531/20/2016 11:0784683485224890:14:00
6477190EOSMKP-188518/17/2016 14:088/26/2016 16:1893342993733965:10:00
6382303EOSMKP-177286/8/2016 16:516/16/2016 17:1890659290985954:27:00
6413632EOSMKP-179386/17/2016 16:596/27/2016 12:3191038091335249:32:00
6407718EOSMKP-178306/17/2016 16:556/27/2016 9:4791037691318846:52:00
6220720EOSMKP-147983/22/2016 19:003/29/2016 18:12876420879120

45:00:00

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

  

W14
W240
W340
W46

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,

0 Replies