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 only the fault which are falling under a specific time stamp

HI

The logic required is as follows :

In the sheet attached in data sheet if you look @ 2 of nov there are two faults.

what is rqequired is i want to calculate on the duration of fault which are started based the time slab mentioned in sheet 2.

"in genral words all the fault's which have a start time betwee 6:00 am to 20:00 0r 8:00 PM"

the out put for 2 nov should be second one

DDDA02/11/2011  03:35:4602/11/2011  12:29:381068
DDDA02/11/2011  12:04:1202/11/2011  12:30:0626

Kindly help us doing this.

Thanks & Regards

Venkatesh Salla

1 Reply
swuehl
MVP
MVP

First you need to link your tables, e.g. by renaming your CustomerAccess to Category or duplicating it:

FAULTS:

LOAD TermId,

     [Customer Access],

     [Customer Access] as Category,

     FaultStartTime,

     FaultEndTime,

     Mins

FROM

Sample.xls

(biff, embedded labels, table is Data$);

ACCESS:

LOAD Category,

     time(alt(AccessStartTime,time#(AccessStartTime,'hh:mm'))) as AccessStartTime,

     time(alt(AccessEndTime,time#(AccessEndTime,'hh:mm'))) as AccessEndTime

FROM

Sample.xls

(biff, embedded labels, table is AccessTime$);

I also needed to take care of your different time formats in your excel file (that's why I needed to use alt() function).

Then just create a straight table chart with dimensions TermId,Customer Access, FaultStartTime and FaultEndTime and use as expression to filter on Category:

=if(frac(FaultStartTime) <= AccessEndTime and frac(FaultStartTime) >= AccessStartTime, Mins,0)

Check the supress zero values is enabled in presentation tab.

Hope this helps,

Stefan