Average time of Employees

[noman212]

Hi guys,

i have the following script which calculate average time of employees i want to calculate average only those whose time is not exceed from 11:00:00AM if anyone time is above 11Am then its exclude this time and calculate only average for those whos time is below 11 . anyone have any idea?

Directory;

For each ExcelFile in filelist ('*.xls')

EOM_HISTORY:

Load

EMP.CODE ,

NAME as EmployeeName,

    DESIGNATION ,

    [D A T E] as Date,

     //TO_DATE as CalendarDate,

Day([D A T E]) AS CalendarDayOfMonth, 

WeekDay([D A T E]) AS CalendarDayName, 

Week([D A T E]) AS CalendarWeekOfYear, 

Month([D A T E]) AS CalendarMonthName, 

'Q' & Ceil(Month([D A T E])/3) AS CalendarQuarter, 

Year([D A T E]) AS CalendarYear,

// Calendar TO_DATE Names 

WeekName([D A T E]) as CalendarWeekNumberAndYear, 

MonthName([D A T E]) as CalendarMonthAndYear, 

QuarterName([D A T E]) as CalendarQuarterMonthsAndYear, 

// Start TO_DATEs 

DayStart([D A T E]) as CalendarDayStart, 

WeekStart([D A T E]) as CalendarWeekStart, 

MonthStart([D A T E]) as CalendarMonthStart, 

QuarterStart([D A T E]) as CalendarQuarterStart, 

YearStart([D A T E]) as CalendarYearStart, 

// End TO_DATEs 

DayEnd([D A T E]) as CalendarDayEnd, 

WeekEnd([D A T E]) as CalendarWeekEnd, 

MonthEnd([D A T E]) as CalendarMonthEnd, 

QuarterEnd([D A T E]) as CalendarQuarterEnd, 

YearEnd([D A T E]) as CalendarYearEnd,

Time(Time#(Num(Hour([T ME IN]),'00')&Num(Minute([T ME IN]),'00')&Num(Second([T ME IN]),'00'), 'hhmmss')) as Time,

ENT.TYPE as status

From $(ExcelFile) (biff, embedded labels, table is [rptCUMTIMESHEETAll.rpt$], filters(Remove(Row, RowCnd(CellValue, 1, StrCnd(equal, 'Information Systems')))))

WHERE WildMatch(EMP.CODE,'1*','2*','3*','4*','5*','6*','7*','8*','9*','10*','11*','12*','13*','14*','15*','16*','17*','18*','19*');

Next ExcelFile;

Inset analysis

=Aggr( avg(Time), EMP.CODE)

[Anonymous]

Maybe like below,

Aggr(Avg(Time), Only({$<Time={"<(=Time(Time#('11:00:00 AM', 'hh:mm:ss TT'))"}>} EMP.CODE))

[noman212]

its show an error nested aggregation not allowed

[ramasaisaksoft]

please add u r QVW file then we will help easily.

[Anonymous]

I see, I hadn't tested that script. You may try below or if possible share some test file to give you perfect solutions.

If(Time<Time(Time#('11:00:00', 'hh:mm:ss')), Aggr(Avg(Time), duration))

[jagan]

Hi,

Try like this

Avg(Aggr(Avg({<Time={"<(=Time(Time#('11:00:00 AM', 'hh:mm:ss TT'))"}>} Time), EMP.CODE))

Or

If you are using EMP.CODE as dimension then use this

Avg({<Time={"<(=Time(Time#('11:00:00 AM', 'hh:mm:ss TT'))"}>} Time)

Hope this helps you.

Regards,

Jagan.