Calculating time formate in expression

[Anonymous]

Hi Friends,

I have Available Hours in Hr Format and Down Time in HH:MM

Need to calculate

1. MTBF =(Availability Hours - Down time) / No of Faults (the result should be in Time period 'HH:MM:SS')

2. Ageing= as per Excel

Please find the excel attached also attached sample app with inline data.

Thanks in advance

Prakash

[Chanty4u]

create a maptable:

as per ur requirment  ..u will get the fields in frnt end.. u cn directly use in chart

//AgingWeekSort:

//mapping load * Inline

//[ Defect_Aging_Weeks,Value

//1 Week,1

//2 Weeks, 2

//> 2 Weeks,3

//]

//;

//

//AgingDaysSort:

//Mapping LOAD * Inline

//[Defect_Aging_DayBands, Value1

//1 Day, 1

//2 Days, 2

//3 Days, 3

//> 3 Days, 4

//]

//;

[daveamz]

Hi Siva,

[Available Hrs] is integer and it needs to be parsed into interval type. Something like Interval#([Available Hrs], 'hh') as [Available Hrs Parsed].

Regards,

David

[jagan]

Hi,

Try like this

LOAD

*,

Num#(Time#([Available Hrs], 'hh:mm:ss')) - Num#(Time#([Down Time]))/[No of Faults] AS AvailHrs;

LOAD Code,

     [Down Time],

     [No of Faults],

     [Available Hrs]  

FROM

[MTBF.xlsx]

(ooxml, embedded labels, table is Sheet1);

Now create a Straight table

Dimension:

Code,

     [Down Time],

     [No of Faults],

     [Available Hrs] ,AvailHrs

Expession: Aging

=If(AvailHrs >= 11, '>11 Days',

If(AvailHrs >= 6, '6 to 10 Days',

If(AvailHrs >= 1, '1 to 5 Days', '<24 Hours')))

[jagan]

PFA file for solution.

Regards,

jagan.

[Anonymous]

Hi all,

Thanks for reply, don't load the excel and please work on which  i'm attached file qvw, because i didn't get available hrs data in time stamp i'm getting in hours like 744. please reply ASAP

[Anonymous]

available time - down time

[settu_periasamy]

Hi,

may be like this..

Edit: removed Ceil

Directory;
T1:
LOAD *,Interval(([Available Hrs]-[Down Time])/[No of Faults],'hh:mm') as MTFB;
LOAD Code,
[Down Time],
[No of Faults],
[Available Hrs]
FROM
[..\MTBF.xlsx]
(ooxml, embedded labels, table is Sheet1);

NoConcatenate
T2:
LOAD *,if(MTFB>10, '11 to 20 Days',
                if(MTFB>5, '6 to 10 Days',
                     if(MTFB>=1, '1 to 5 Days',
                     '<=24 hours'))) as Ageing Resident T1;
Drop Table T1;

[settu_periasamy]

Hi,

Try this..

T1:
LOAD *,if(MTFB>10, '11 to 20 Days',
  if(MTFB>5, '6 to 10 Days',
  if(MTFB>=1, '1 to 5 Days',
  '<=24 hours'))) as Ageing;
LOAD *,Interval((Interval#([Available Hrs],'hh')-Interval#([Down Time],'hh:mm'))/[No of Faults],'hh:mm') as MTFB;
LOAD * INLINE [
    Code,Down Time,No of Faults,Available Hrs
    1, 64:29, 23, 744
    2, 92:02, 22, 744
    3, 51:58, 19, 744
    4, 28:09, 19, 744
    5, 191:27, 18, 744
    6, 150:24, 17, 744
    7, 119:24, 16, 744
    8, 542:49, 16, 744
    9, 21:25, 6, 744
    10, 8:33, 6, 744
    11, 3:51, 3, 744
    12, 3:49, 3, 744
    13, 21:43, 3, 744
];

[jagan]

HI,

Please try below script

LOAD *,

If(MTFB > 10, '> 11 Days',

  If(MTFB > 5, '6 - 10 Days',

  If(MTFB >= 1, '1 - 5 Days', '<24 Hrs'))) AS AgeBucket;

LOAD

  Code,

     [Down Time],

     [No of Faults],

     [Available Hrs],

Interval#(AvailHrs - DownHrs)/[No of Faults] AS MTFB;

LOAD Code,

     [Down Time],

     [No of Faults],

     [Available Hrs],

     Interval#([Available Hrs], 'hh') AS AvailHrs,

      Interval#([Down Time], 'hh:mm') AS DownHrs; 

LOAD * INLINE [ 

    Code,Down Time,No of Faults,Available Hrs 

    1, 64:29, 23, 744 

    2, 92:02, 22, 744 

    3, 51:58, 19, 744 

    4, 28:09, 19, 744 

    5, 191:27, 18, 744 

    6, 150:24, 17, 744 

    7, 119:24, 16, 744 

    8, 542:49, 16, 744 

    9, 21:25, 6, 744 

    10, 8:33, 6, 744 

    11, 3:51, 3, 744 

    12, 3:49, 3, 744 

    13, 21:43, 3, 744 

];