Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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 .

9 Replies
anbu1984
Master III
Master III

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?

Anonymous
Not applicable
Author

with what should we compare the AU_TIME?

Not applicable
Author

Each Product line have different AU_Time.

Like in column:

Product Line     ,AU_Time ,        Status

                      

Eclipse2014-01-28 21:53:51Closed
Eclipse2014-01-28 21:53:51ReTest
Eclipse2014-01-28 21:53:51Closed
Eclipse2014-01-28 22:28:28Closed

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.

Not applicable
Author

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

                      

Eclipse2014-01-28 21:53:51Closed
Eclipse2014-01-28 21:53:51ReTest
Eclipse2014-01-28 21:53:51Closed
Eclipse2014-01-28 22:28:28Closed

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.

Not applicable
Author

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)

Not applicable
Author

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

                      

Eclipse22          ,   0Closed
Eclipse32   ,     10   ReTest

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

Same steps for other entries also

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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)