topic Interval between time start and end in QlikView

Interval between time start and end

marcohadiyanto — Tue, 06 Mar 2012 06:28:19 GMT

Hi All,

i'm trying to get interval between time start and time end (StartTime) - (EndTime), my time format is 24 hours. when StartTime greater than EndTime, it's fine, but if EndTime greater than StartTime, the interval function always doing the minimum interval.

ex 1:

StartTime : 10:00:00

EndTime : 12:00:00

interval : 2:00

---------------------------------> it's work perfect it's calculate between 10AM to 12 PM.

ex2:

StartTime : 10:00:00

EndTime : 02:00:00

interval 8:00

--------------------------------->it's Error because it's only calculate between 10AM to 2AM. the result i want is 16:00

what's expression to make this interval correctly?

regards,,

Marco

Interval between time start and end

CELAMBARASAN — Tue, 06 Mar 2012 06:38:17 GMT

Hi,

You have AM and PM notation in that time period?

I mean

StartTime : 10:00:00 AM

EndTime : 02:00:00 PM

Celambarasan

Interval between time start and end

marcohadiyanto — Tue, 06 Mar 2012 06:44:02 GMT

Hi,

i dont have AM and PM notation, but the time format already 24hour.

Regards,

Interval between time start and end

CELAMBARASAN — Tue, 06 Mar 2012 06:51:02 GMT

Hi,

It has only time?No date before it?

because as per your case 10:00:00 is on the day '03/05/2012' (March 05) means then '02:00:00' willbe on March 06.am i right?

Celambarasan

Interval between time start and end

jagan — Tue, 06 Mar 2012 06:53:30 GMT

Hi,

If the Time1 and Time2 are times of different dates then there should date should also be there in the field. Then only it is possible.

By using Interval() the difference is calculated.

Regards,

Jagan.

Interval between time start and end

perumal_41 — Tue, 06 Mar 2012 06:54:57 GMT

Hi,

Try This Method

if( EndTime<StartTime,Time('24:00:00','h:mm:ss ')-(EndTime-StartTime),EndTime-StartTime)

Regards

Perumal

Interval between time start and end

marcohadiyanto — Tue, 06 Mar 2012 06:57:30 GMT

"Hi,

It has only time?No date before it?

because as per your case 10:00:00 is on the day '03/05/2012' (March 05) means then '02:00:00' willbe on March 06.am i right?

Celambarasan"

Hi, yes you right. it's like that. i dont have the date value only time. but my time it's can't over 24 hours. the max betweek start and end is 24 Hours.

Regards,

Interval between time start and end

jagan — Tue, 06 Mar 2012 06:58:32 GMT

Hi,

Check this example

=interval('2012-03-02 10:00:00'- '2012-03-01 02:00:00 ', 'hh:mm' )

Regards,

Jagan.

Interval between time start and end

marcohadiyanto — Tue, 06 Mar 2012 07:00:47 GMT

hi Jagan,

i dont have date value, it's only time value. if i have date value then it don't have problem with this..

any other to make it happen?

Regards,

Marco

Interval between time start and end

jagan — Tue, 06 Mar 2012 07:04:36 GMT

Hi,

If you don't have the date, then how would you know that both dates has difference of 1day or 2days.

Check whether you have Start and End date in any other columns, if you have then we can append the date and time and do this calculation.

Can you post the sample data.

Regards,

Jagan.

Interval between time start and end

marcohadiyanto — Tue, 06 Mar 2012 07:08:31 GMT

Hi,

Try This Method

if( EndTime<StartTime,Time('24:00:00','h:mm:ss ')-(EndTime-StartTime),EndTime-StartTime)

Regards

Perumal"

hi, thanks for helping, but it's not working. when i'm trying to use Time('24:00:00','h:mm:ss ') it show 00:00:00 not 24:00:00 that's why the result is the same like we're doing (EndTime-StartTime)

Regards,

Marco

Interval between time start and end

CELAMBARASAN — Tue, 06 Mar 2012 07:13:27 GMT

Hi,

Try with this expression

=interval(if(Time(StartTime)<Time(EndTime),Today()+Time(EndTime),(Today()+1)+Time(EndTime))-(today()+Time(StartTime)),'hh:mm:ss')

Hope it helps

Celambarasan

Interval between time start and end

perumal_41 — Tue, 06 Mar 2012 07:18:16 GMT

Hi,

Sorry for Small Mistake .Now Try This Method

Time((Time('23:59:59','h:mm:ss ') -Time('8:00:00','h:mm:ss '))+Time('00:00:01','h:mm:ss '),'h:mm:ss ')

Regards

Perumal

Re: Interval between time start and end

marcohadiyanto — Tue, 06 Mar 2012 07:23:31 GMT

Hi Jagan,

here i give my sample data.

it's similiar like this.

Thank You,

Marco

Re: Interval between time start and end

marcohadiyanto — Tue, 06 Mar 2012 07:32:46 GMT

Hi Celam,

it works perfect..

Thanks you so much

Interval between time start and end

CELAMBARASAN — Tue, 06 Mar 2012 07:37:03 GMT

Hi,

Im just adding date to the time.if the endtime is lesser than the start time then increase the date by 1.

Celambarasan

Interval between time start and end

marcohadiyanto — Tue, 06 Mar 2012 08:31:54 GMT

Hi,

what if i want to know sum of interval hour?

can i just add sum like this :

=sum(interval(if(Time(StartTime)<Time(EndTime),Today()+Time(EndTime),(Today()+1)+Time(EndTime))-(today()+Time(StartTime)),'hh:mm:ss')) ?

i tried with that no error line but it the status "error in expression"

any idea?

Regards,

Marco

Interval between time start and end

CELAMBARASAN — Tue, 06 Mar 2012 08:36:37 GMT

Hi,

Can you please tel me what dimensions you have used?

Celambarasan

Re: Interval between time start and end

marcohadiyanto — Tue, 06 Mar 2012 08:51:17 GMT

hi,

after we get the result of interval, i want to sum it. my dimension is month.

here is my expression. when i put sum on this it's error.

=Floor(num#(Interval(if(Time#(num(Only({<STAGR={'MTBJOS'}>}SMEBTR)*100,'0000'),'hhmm') <Time#(num(Only({<STAGR={'MTBJOF'}>}SMEBTR)*100,'0000'),'hhmm'),
Today()+Time#(num(Only({<STAGR={'MTBJOF'}>}SMEBTR)*100,'0000'),'hhmm'),(Today()+1)+Time#(num(Only({<STAGR={'MTBJOF'}>}SMEBTR)*100,'0000'),'hhmm'))
-(Today()+Time#(num(Only({<STAGR={'MTBJOS'}>}SMEBTR)*100,'0000'),'hhmm')),'hhmm'))/100)

Regards,

Marco

Re: Interval between time start and end

CELAMBARASAN — Tue, 06 Mar 2012 08:53:50 GMT

Hi,

Do you have Many start and end dates for each Months?

if so try with aggr
=Sum(aggr(Expr,StartTime,EndTime,Month))

Hope it helps

Celambarasan