topic Re: Time Differnce in QlikView

Time Differnce

Thu, 15 May 2014 09:52:51 GMT

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 — Thu, 15 May 2014 10:26:43 GMT

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

[

];

////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)

Thu, 15 May 2014 10:55:19 GMT

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 — Thu, 15 May 2014 10:59:03 GMT

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))