Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
Maybe like below,
Aggr(Avg(Time), Only({$<Time={"<(=Time(Time#('11:00:00 AM', 'hh:mm:ss TT'))"}>} EMP.CODE))
its show an error nested aggregation not allowed
please add u r QVW file then we will help easily.
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))
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.