topic Re: Hours Calculation in QlikView

Hours Calculation

hari8088 — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I have data like Employee name,Start time,End time and use are the Columns.

Here my output like

Output Format:

This is example only not exact output

Mailchecking Meeting Reportpreparing

Hour1 15.00 45.00 0.00

Hour2

Hour3

Hour4

Hour5

Hour6

Hour7

Hour8

Hour9

Want to calculate employee timings(how much time they spend) hour by hour by using their start time(here employee start time is different for each one).

For example:

Employee A his start time is 8:30 AM

Then he spent 15 min for mail checking and then next one hour he spend time for report sending.(8:45 to 9:45)

From 9:45 to 10:45 he spent time for meeting.

My output like:

Mailchecking Reportsending Meeting

Hour1 15.0 45.00 0

Hour2 0 15.00 45

Because Hour1 will be between 8:30 to 9:30

Means every hour we have to differentiate and have to show their use in above format.

Here am attaching sample data

Thanks in advance.

Re: Hours Calculation

el_aprendiz111 — Mon, 05 Jun 2017 19:32:36 GMT

good afternoon

attached qvw if it serves you.

Re: Hours Calculation

ziadm — Mon, 05 Jun 2017 19:51:46 GMT

Attached is how to do it

Re: Hours Calculation

ziadm — Mon, 05 Jun 2017 20:00:01 GMT

Table1:

LOAD Date as WorkinDate,

Empname,

Monitaring,

Startrime,

Endtime,

Interval(Endtime -Startrime ,'mm') as TimeTaken,

Use

FROM

(ooxml, embedded labels, table is Sheet1);

Create a Chart Table where your Dimensions are

WorkingDate

Empname

Use

and your Measure will be Sum(TimeTaken)

Re: Hours Calculation

hari8088 — Tue, 06 Jun 2017 14:27:14 GMT

Hi Fer,

Good evening,Thanks for your replay.

This is not what am looking for this scenario.

My dimension will be hour that will contain

Hour

.......

Hour1

Hour2

Hour3

.Hour9

This is the field that need to bring then the usage off all employees between one hour should come under Hour1 like this.

Re: Hours Calculation

hari8088 — Tue, 06 Jun 2017 14:28:53 GMT

Hi Ziad,

Good evening,Thanks for your replay.

This is not what am looking for this scenario.

My dimension will be hour that will contain

Hour

.......

Hour1

Hour2

Hour3

.Hour9

This is the field that need to bring then the usage off all employees between one hour should come under Hour1 like this.

Re: Hours Calculation

el_aprendiz111 — Tue, 06 Jun 2017 14:55:59 GMT

Hi Hari

Re: Hours Calculation

tajmohamed30 — Tue, 06 Jun 2017 15:02:09 GMT

Have you checked interval match function

Re: Hours Calculation

ahaahaaha — Tue, 06 Jun 2017 15:10:23 GMT

Hi,

May be like in attached file

Andrey

Re: Hours Calculation

hari8088 — Tue, 06 Jun 2017 15:37:13 GMT

Hi Fer,

This is the output i required.

Thanks,

Re: Hours Calculation

el_aprendiz111 — Tue, 06 Jun 2017 15:56:26 GMT

Hi Hari

in the script

Interval(Endtime-Startrime,'hh:mm:ss') AS DURATION,

Re: Hours Calculation

hari8088 — Tue, 06 Jun 2017 16:12:34 GMT

Hi Andrey,

Not exact this

Have attached the output in my previous post

Re: Hours Calculation

ziadm — Tue, 06 Jun 2017 17:15:39 GMT

How about this

Re: Hours Calculation

tajmohamed30 — Wed, 07 Jun 2017 11:38:42 GMT

Hi Hari,

Please find the attached sample. Hope it will help.

Regards

Taj Mohamed