Problem in multi selection of years

[#1]

I want display worked hours per EMP_ID  month wise , year wise and multi selection of months and years alone.
employee worked hours enter daily basis
here condition is worked hours <4 is available,>=4&<8 is partially available,>=8 is occupied.
could you please tell me script level

Regards

Niranjan

[#2]

Here single selection working properly. Condition is t_worked hrs is < 4 available, >4&<8 is partially available and >8 is occupied. See first table  EMP_ID 101 worked hours are 4+4=8 month is FEB

Round (sum (Workedhours)/ (count (DISTINCT (Sheetdate)))) as T_Worked
Resident ppp Group by EMP_ID, monthname; 

See second table 101 employee data showing correctly

Upto now I used script is

ppp:
LOAD EMP_ID,
Workedhours,
Sheetdate,
MonthName(Sheetdate)as monthname,
Year(Sheetdate)as year
FROM

(ooxml, embedded labels, table is Sheet1);




Totalmonth:
LOAD
EMP_ID,
monthname,
Round(sum(Workedhours)/(count(DISTINCT(Sheetdate)))) as T_Worked
Resident ppp Group by EMP_ID,monthname;

New:
load *,
if(T_Worked<4,'Available',if(T_Worked>=4 and T_Worked<8,'Partially_available',if(T_Worked>=8,'occupied')))as Allocationlevel resident Totalmonth;

QuartersMap: 
MAPPING LOAD  
rowno() as Month, 
'Q' & Ceil (rowno()/3) as Quarter 
AUTOGENERATE (12); 

Temp: 
Load 
min(Sheetdate) as minDate, 
max(Sheetdate) as maxDate 
Resident ppp; 

Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 
DROP Table Temp; 

TempCalendar: 
LOAD 
$(varMinDate) + Iterno()-1 As Num, 
Date($(varMinDate) + IterNo() - 1) as TempDate 
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

MasterCalendar: 
Load 
TempDate AS Sheetdate, 
week(TempDate) As Week, 
Year(TempDate) As Year, 
Month(TempDate) As Month, 
Day(TempDate) As Day, 
YeartoDate(TempDate)*-1 as CurYTDFlag, 
YeartoDate(TempDate,-1)*-1 as LastYTDFlag, 
inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 
date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, 
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 
WeekDay(TempDate) as WeekDay 
Resident TempCalendar 
Order By TempDate ASC; 
Drop Table TempCalendar; 

Problem is explained below

When I select Feb month need average T_worked for all years respective of month but now showing wrong

And when I select multiple years employee come under the one particular thing like available or partially available or occupied

[Anonymous]

I think you need to have a month name of just Feb instead of Feb 2015 so that all selected years get calculated together.

And then have your calculation for Available or Partially Available in your straight table as an expression that refers to the hours Column.

[#4]

Here single selection working properly. Condition is t_worked hrs is < 4 available, >4&<8 is partially available and >8 is occupied. See first table  EMP_ID 101 worked hours are 4+4=8 month is FEB

Round (sum (Workedhours)/ (count (DISTINCT (Sheetdate)))) as T_Worked
Resident ppp Group by EMP_ID, monthname;

See second table 101 employee data showing correctly

Upto now I used script is

ppp:
LOAD EMP_ID,
Workedhours,
Sheetdate,
MonthName(Sheetdate)as monthname,
Year(Sheetdate)as year
FROM

(ooxml, embedded labels, table is Sheet1);




Totalmonth:
LOAD
EMP_ID,
monthname,
Round(sum(Workedhours)/(count(DISTINCT(Sheetdate)))) as T_Worked
Resident ppp Group by EMP_ID,monthname;

New:
load *,
if(T_Worked<4,'Available',if(T_Worked>=4 andT_Worked<8,'Partially_available',if(T_Worked>=8,'occupied')))asAllocationlevel resident Totalmonth;

QuartersMap: 
MAPPING LOAD  
rowno() as Month, 
'Q' & Ceil (rowno()/3) as Quarter 
AUTOGENERATE (12); 

Temp: 
Load 
min(Sheetdate) as minDate, 
max(Sheetdate) as maxDate 
Resident ppp; 

Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 
DROP Table Temp; 

TempCalendar: 
LOAD 
$(varMinDate) + Iterno()-1 As Num, 
Date($(varMinDate) + IterNo() - 1) as TempDate 
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

MasterCalendar: 
Load 
TempDate AS Sheetdate, 
week(TempDate) As Week, 
Year(TempDate) As Year, 
Month(TempDate) As Month, 
Day(TempDate) As Day, 
YeartoDate(TempDate)*-1 as CurYTDFlag, 
YeartoDate(TempDate,-1)*-1 as LastYTDFlag, 
inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 
date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, 
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 
WeekDay(TempDate) as WeekDay 
Resident TempCalendar 
Order By TempDate ASC; 
Drop Table TempCalendar; 

Problem is explained below

When I select Feb month need average T_worked for all years respective of month but now showing wrong

And when I select multiple years employee come under the one particular thing like available or partially available or occupied