Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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
Honored Contributor III

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

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?

ravic906
Contributor III

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

with what should we compare the AU_TIME?

Not applicable

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

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

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

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

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

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

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

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

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

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

Not applicable

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

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

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

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)

Community Browser