Showing Monthwise and Daywise Granualarity

[yoganantha321]

Hi,

I have the following data:

Date	EMPID	WorkingHRS
01/15/2017	3	8
01/26/2017	3	4
01/31/2017	3	6
02/14/2017	3	8
02/17/2017	3	5
03/29/2017	3	2
03/30/2017	3	6
03/31/2017	3	8
01/15/2017	4	8
01/26/2017	4	4
01/31/2017	4	6
02/14/2017	4	8
02/17/2017	4	5
03/29/2017	4	2
03/30/2017	4	6
03/31/2017	4	8
01/15/2017	5	8
01/26/2017	5	4
01/31/2017	5	6
02/14/2017	5	8
02/17/2017	5	5
03/29/2017	5	2
03/30/2017	5	6
03/31/2017	5	8

I have to show a chart with master calendar year, month and date wise.

with bucket (worked hours  >7 as Occupied, worked hours >4 and worked hours<7 as Partially Occupied, worked hours <4 as Available)

I want to show date wise and

also while in month wise it must sum worked hours of the whole month data of  particular employee divided by sum of dates entered and show the level(Occupied or Partially Occupied or Available).

Eg:

Month Wise:

Jan 2017

Employee Id : 3 data (8+4+6)=18 /3(No.of date entry)=9     9 falls in bucket Occupied.

Date Wise:

01/15/2017	3	8	Occupied
01/26/2017	3	4	Partially Occupied
01/31/2017	3	6	Partially Occupied

[neelamsaroha157 · Accepted solution]

Check out the attached file.

You can play with the aggr to meet your exact requirement.