# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for
Did you mean:
Partner

## Show minutes used in Hour

Hi all,

I have a table that looks like this

IDDateStart TimeEnd Time
A11/8/20179:30:0010:30:00
B11/8/201711:30:0013:00:00
C11/9/20177:30:0010:00:00
D11/9/201713:30:0014:00:00

I am looking to preform some back end scripting to get the following result

IDDateHourMinutes Used in Hour

A

11/8/2017830
A11/8/2017930
B11/8/20171030
B11/8/20171160
C11/9/2017630
C11/9/2017760
C11/9/2017860
D11/9/20171230

What I am looking for is to show how many minutes were used in each hour. For example

ID C starts at 7:30 am and ends at 10:00 am.

30 minutes is used in the 6th hour, 60 minutes used in the 9th hour, and 60 mintues used in the 10th hour.

(The hour count starts at 0 so midnight-1 am is considered hour 0, 1am-2am is considered hour 1 etc.)

Please let me know if you have any ideas!!

1 Solution

Accepted Solutions
MVP

Maybe like this

Utilization per hour

15 Replies
MVP

May be this

Table:

Hour([Start Time] + MakeTime(IterNo() - 1))-1 as Hour,

Interval(If([Start Time] >= [Start Time] + MakeTime(IterNo() - 1), Ceil([Start Time], 1/24) - [Start Time],

If([End Time] <= [Start Time] + MakeTime(IterNo() - 1), [End Time] - Floor([End Time], 1/24), 1/24)), 'mm') as [Minutes Used in Hour]

While [Start Time] + MakeTime(IterNo() - 1) <= [End Time];

ID, Date, Start Time, End Time

A, 11/8/2017, 9:30:00, 10:30:00

B, 11/8/2017, 11:30:00, 13:00:00

C, 11/9/2017, 7:30:00, 10:00:00

D, 11/9/2017, 13:30:00, 14:00:00

];

Contributor III

is like this?

Partner
Author

Sunny,

This is close. when applied to my real data set it seems that some hours are getting left out. For example I have a ID that has the following

ID,Date,Start,End

A,2017-06-03,7:30:00,9:08:00

The result this code is giving me is the following

ID,Date,Hour,Start,End

A,2017-06-03,6,30

A,2017-06-03,7,60

However there is no row for the 8th hour with 8 mins.

Partner
Author

Did you mean to attach a link?

MVP

Try this

Table:

Where [Minutes Used in Hour] <> 0;

Hour([Start Time] + MakeTime(IterNo() - 1))-1 as Hour,

Interval(If([Start Time] >= [Start Time] + MakeTime(IterNo() - 1), Ceil([Start Time], 1/24) - [Start Time],

If([End Time] <= [Start Time] + MakeTime(IterNo() - 1), [End Time] - Floor([End Time], 1/24), 1/24)), 'mm') as [Minutes Used in Hour]

While Hour([Start Time] + MakeTime(IterNo() - 1)) <= Hour([End Time]);

ID, Date, Start Time, End Time

A, 11/8/2017, 9:30:00, 10:30:00

B, 11/8/2017, 11:30:00, 13:00:00

C, 11/9/2017, 7:30:00, 10:00:00

D, 11/9/2017, 13:30:00, 14:00:00

E, 3/6/2017,  7:30:00,  9:08:00

];

Partner
Author

Sunny,

This is giving me the same issue. Some hours usually the last hour is getting dropped off

MVP

Maybe like this

Utilization per hour

Partner
Author

Is there a way to do this by half hour intervals instead of full hour?

MVP

Yes, should be possible, chnage the code to something like

Iterno() as BucketID,

Time(Floor(Start_time+(iterno()-1)*MakeTime(0,30),MakeTime(0,30))) as Bucket_Time,

Round(

(RangeMin(End_time,Floor(Start_time+(iterno())*MakeTime(0,30), MakeTime(0,30)))

-RangeMax(Start_time,Floor(Start_time+(iterno()-1)*MakeTime(0,30), MakeTime(0,30))))

*60*24) as DurationBucket

Resident INPUT

WHILE Floor(Start_time+(iterno()-1)*MakeTime(0,30),MakeTime(0,30)) < End_time;