topic Re: How to calculate the time difference from the same column values . in QlikView

How to calculate the time difference from the same column values .

Tue, 27 May 2014 04:38:09 GMT

Hi ,

I want to calculate the Time difference of the values from the AU_TIME.

Then check the status for how much time the status was there .

I have attached the excel file also please check .

Thanks .

Re: How to calculate the time difference from the same column values .

anbu1984 — Tue, 27 May 2014 05:16:48 GMT

These two timestamps 1/28/2014 9:53:51 PM, 2014-01-28 21:53:51 are same values in different format. Can you explain how you calculate Closed status time?

Re: How to calculate the time difference from the same column values .

Anonymous — Tue, 27 May 2014 05:55:23 GMT

with what should we compare the AU_TIME?

Re: How to calculate the time difference from the same column values .

Tue, 27 May 2014 06:11:52 GMT

Each Product line have different AU_Time.

Like in column:

Product Line ,AU_Time , Status

Eclipse	2014-01-28 21:53:51	Closed
Eclipse	2014-01-28 21:53:51	ReTest
Eclipse	2014-01-28 21:53:51	Closed
Eclipse	2014-01-28 22:28:28	Closed

I need calculate first deffrnc with AU_Time foreach row.

(2014-01-28 21:53:51 - 2014-01- 28 21:53:51) in hours =12hrs. .

then will say that Eclipse was in closed status for 12 hours.

then next we will calculate (2014-01-28 21:53:51 - 2014-01-28 21:53:51)in hrs =0hrs so the status will be retest .

Eclipse remain in retest status for 0 hrs.

Re: How to calculate the time difference from the same column values .

Tue, 27 May 2014 06:17:10 GMT

Status is given already in the table no need to calculate it these three columns are provided already.you need to calculate like:

Each Product line have different AU_Time.

Like in column:

Product Line ,AU_Time , Status

Eclipse	2014-01-28 21:53:51	Closed
Eclipse	2014-01-28 21:53:51	ReTest
Eclipse	2014-01-28 21:53:51	Closed
Eclipse	2014-01-28 22:28:28	Closed

I need calculate first deffrnc with AU_Time foreach row.

(2014-01-28 21:53:51 - 2014-01- 28 21:53:51) in hours =12hrs. .

then will say that Eclipse was in closed status for 12 hours.

then next we will calculate (2014-01-28 21:53:51 - 2014-01-28 21:53:51)in hrs =0hrs so the status will be retest .

Eclipse remain in retest status for 0 hrs.

Re: How to calculate the time difference from the same column values .

Tue, 27 May 2014 06:28:39 GMT

HI Try this,

First of all fetch hour from given time stamp in back end script using:

example

Main:

LOAD [Product Line],

AU_TIME,

Hour(AU_TIME) as hour,

Status

FROM

(ooxml, embedded labels, table is Sheet1);

in chart add dimension AU_TIME as dimension & add below expression:-

=hour-Above(hour)

Re: How to calculate the time difference from the same column values .

Tue, 27 May 2014 06:40:03 GMT

Thanks Shweta ,

I am able to get the difference in time now.But how to calculate the status time .for eg

Product Line ,AU_Time , Differnce Status

Eclipse	22 , 0	Closed
Eclipse	32 , 10	ReTest

The Product Line Eclipse was in Status Closed For 10 hours .

Same steps for other entries also

Re: How to calculate the time difference from the same column values .

Tue, 27 May 2014 06:52:44 GMT

tell me your logic i mean on which basis you want to calculate status

Re: How to calculate the time difference from the same column values .

Tue, 27 May 2014 07:51:20 GMT

Status is already in the column i just need to calculate the time taken by each status.

The difference is the difference between AU_Time . then we will check that Status was closed for 10 hours .then it changed to Retest

Re: How to calculate the time difference from the same column values .

Tue, 27 May 2014 09:23:11 GMT

Add one more expression:-

If(column(1)=10,'Closed','Reset') & name this expression as Status

Note:-Where column(1) is column no of difference i.e hour-above(hour)