topic Re: Calculate time difference in QlikView

Calculate time difference

narender123 — Wed, 02 Mar 2016 05:40:40 GMT

Hi all ,

I have to calculate the time differnce between two dates . Likes:

Column1 Column2

09-09-2014 13:28:46 41891.576825799

Calculate time formate like hh:mm:ss

Both columns are date column .Column2 is in Num format of date.

Please suggest.

Thanks,

Narender

Re: Calculate time difference

PrashantSangle — Wed, 02 Mar 2016 05:45:25 GMT

Use interval()

For Example:

Interval(time2-time1,hh:mm)

For details see it in help menu.

Regards

Re: Calculate time difference

jagan — Wed, 02 Mar 2016 05:46:03 GMT

Hi,

Try Interval()

Regards,

Jagan.

Re: Calculate time difference

settu_periasamy — Wed, 02 Mar 2016 05:47:49 GMT

Hi,

Try this..

=Interval(41891.576825799-TimeStamp#('09-09-2014 13:28:46','DD-MM-YYYY hh:mm:ss'),'hh:mm:ss')

Re: Calculate time difference

Anonymous — Wed, 02 Mar 2016 05:48:30 GMT

may be like this?

Use TimeStamp() at script, like?

TimeStamp#(Coulmn2, 'DD-MM-YYYY hh:mm:ss') as Column2DateTime

Now to find out the difference between two date column, try like this?

Interval(Column1 - Column2DateTime, 'hh:mm:ss') as Diff

Re: Calculate time difference

tamilarasu — Wed, 02 Mar 2016 05:59:49 GMT

You can also try,

Interval(Column1 - Num(Date#(Column2, 'DD-MM-YYYY hh:mm:ss')),'hh:mm:ss')

Re: Calculate time difference

Anonymous — Wed, 02 Mar 2016 06:29:42 GMT

interval(column2 - column1,'DD-MM-YYYY hh:mm:ss') as differencetime

Re: Calculate time difference

narender123 — Wed, 02 Mar 2016 06:40:35 GMT

Hi All,

Thanks you all.

I got the difference.

for total i have selected sum of rows in expression tab .So now it is showing total also.

But i want the Avg of all the time difference rather than toatl of "hh:mm:ss" means "sum of interval differnce/total number of entries".

Correct me if i am wrong.

Narender

Re: Calculate time difference

narender123 — Wed, 02 Mar 2016 06:41:48 GMT

Thank you.

Please see my reply to Jagan Mohan ,i have one more question.

Narender

Re: Calculate time difference

narender123 — Wed, 02 Mar 2016 06:41:58 GMT

Thank you.

Please see my reply to Jagan Mohan ,i have one more question.

Narender

Re: Calculate time difference

narender123 — Wed, 02 Mar 2016 06:42:07 GMT

Thank you.

Please see my reply to Jagan Mohan ,i have one more question.

Narender

Re: Calculate time difference

narender123 — Wed, 02 Mar 2016 06:42:29 GMT

Thank you.

Please see my reply to Jagan Mohan ,i have one more question.

Narender

Re: Calculate time difference

settu_periasamy — Wed, 02 Mar 2016 06:44:35 GMT

Hi,

Did you try the Average instead of Sum in the Expression Tab?

Re: Calculate time difference

Digvijay_Singh — Wed, 02 Mar 2016 06:45:35 GMT

Try selecting Avg of rows in Expressions/Total Mode option.

Re: Calculate time difference

narender123 — Wed, 02 Mar 2016 06:57:51 GMT

Hi Settu,

I have tried but it is showing same result as total

Re: Calculate time difference

narender123 — Wed, 02 Mar 2016 06:58:09 GMT

Hi Digvijay,

I have tried but it is showing same result as total.

Re: Calculate time difference

Anonymous — Wed, 02 Mar 2016 07:02:42 GMT

By using the interval function it is working

Re: Calculate time difference

settu_periasamy — Wed, 02 Mar 2016 07:11:16 GMT

Can you post the sample data? or screen shot?

Re: Calculate time difference

jagan — Wed, 02 Mar 2016 07:18:55 GMT

Hi,

The date difference is in different format when compare to numbers. How you want the sum to display in terms of Hours or Days?

For Days:

Ceil(Num(Time2 - Time1) )

For Hours:

Ceil(Num(Time2 - Time1) * 24 )

Hope this helps you.

Regards,

jagan.

Re: Calculate time difference

PrashantSangle — Wed, 02 Mar 2016 08:12:53 GMT

Hi,

As other people suggested Expression Tab -> Total Mode -> Average of rows must work.

Can you give provide screen shot of your chart after applying above said suggestion.

Regards