Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
DDD | A | 02/11/2011 03:35:46 | 02/11/2011 12:29:38 | 1068 |
DDD | A | 02/11/2011 12:04:12 | 02/11/2011 12:30:06 | 26 |
Kindly help us doing this.
Thanks & Regards
Venkatesh Salla
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