Time Differnce

[#1]

Hi,

I have following data-:

Start Time  Stop Time  Site ID

00.00.00     00.00.16     s1

00.00.01      00.00.17    s2

00.00.02      00.00.22    s3

.                  .                .

.                  .                .

.                   .               .

.                   .               .

01.00.01       01.00.00     s20

So  i  want number of site id those are in between the 12.00 am to 1.00 am

how i can calculate this?

[MK_QSL]

Create a flag in your script something as below...

Load
	ID,
	Time(StartTime) as StartTime,
	Time(EndTime) as EndTime,
	If(StartTime > Time#('12:00:00','hh:mm:ss') and EndTime < Time#('13:00:00','hh:mm:ss'),1,0) as Flag

Inline

[

	ID,	StartTime,	EndTime
	1,	10:00:00,	11:00:00
	2,	10:12:12,	10:25:00
	3,	12:05:00,	12:10:10
	4,	12:25:00,	12:20:20
	5,  12:50:00,	13:00:00
	6,  13:55:00,	14:00:00

];

////NOTE : if you want to include 12 and 13 oclock also use >= and <= instead of > and <,

Now you can use as below to find our your ID

=COUNT(DISTINCT {<Flag = {1}>}ID)

[#3]

Hi,

Manish

i want  expression for randomly choose any time that means (dynamic) e.g. 2.00 pm to 4.00 or 3.00 pm to 4.00 pm

i have given data till 01.00.00 pm for reference purpose.

[MK_QSL]

Use below in Text Box

=COUNT(DISTINCT AGGR(If(StartTime > Time#('12:00:00','hh:mm:ss') and EndTime < Time#('13:00:00','hh:mm:ss'),1,0),ID))