1 Reply Latest reply: Feb 6, 2012 5:18 AM by Stefan Wühl RSS

    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

        • Re: How to calculate only the fault which are falling under a specific time stamp
          Stefan Wühl

          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