topic Find available employees in Management & Governance

Find available employees

akshada_samant — Tue, 30 Oct 2018 09:41:03 GMT

Hi,

I have a big data set, below is a small sub-set of it:

Sr_No	ID	Client	Project	Start Date	End Date	FTE	Name
250	1	Client 1	Training	1/29/2018	2/4/2018	1	ABC
1722	1	Client 2	Implementation	4/2/2018	7/29/2018	0.2	ABC
1723	1	Client 3	Implementation	2/5/2018	4/1/2018	1	ABC
1724	1	Client 4	Implementation	7/30/2018	3/31/2019	0.2	ABC
2844	1	Client 3	Phase2	4/2/2018	12/30/2018	0.8	ABC
232	2	Client 4	Internal Operations	2/1/2016	6/30/2019	0.1	DEF
1958	2	Client 5	Phase 1	11/27/2017	4/22/2018	0.5	DEF
1959	2	Client 6	Phase 1	4/23/2018	12/30/2018	0.5	DEF
737	3	Client 7	Project 1	1/22/2018	2/25/2018	0.5	XYZ
944	3	Client 8	Project 2	2/26/2018	10/28/2018	0.5	XYZ
1378	3	Client 9	Project 3	4/13/2015	4/26/2020	0.25	XYZ
1386	3	Client 10	Project 4	8/29/2016	4/26/2020	0.25	XYZ

In the above data:

1) FTE is showing the % of time the resource is tagged/allotted to any particular project. So 1 means 100% and 0.5 means 50%.

2) Start date is the start date when a particular employee is tagged to any project.

3) End date is the date when the employee will be released from the tagged project.

Requirement : I want to find which resources are completely or partially available as per today. I want to get the details of employees whose project end date is greater than today's date and sum of FTE is less than 1.

In the above sample data, for empid 1: there are 2 rows whose date is more than today's date but the FTE summation comes to 1, that means he is allotted to projects and not available. Where for empid 2 and emp id 3, there are rows whose summation of FTE's whose date is is more than today is less than 1. That means they are not completely allotted and somewhat free.

Employee may be tagged in any project/client that doesn't matter we have to check whether he is 100% allotted or any time free so can be allotted to other project/client.

I should get below output:

Sr_No	ID	Client	Project	Start Date	End Date	FTE	Name
232	2	Client 4	Internal Operations	2/1/2016	6/30/2019	0.1	DEF
1959	2	Client 6	Phase 1	4/23/2018	12/30/2018	0.5	DEF
1378	3	Client 9	Project 3	4/13/2015	4/26/2020	0.25	XYZ
1386	3	Client 10	Project 4	8/29/2016	4/26/2020	0.25	XYZ

Note: Date is in MM/DD/YYYY format.

Kindly help me to implement the requirement. Thanks in advance.

Regards,

Akshada

Re: Find available employees

sunny_talwar — Tue, 30 Oct 2018 10:55:38 GMT

Try one of these expressions

Sum({<[Start Date] = {"$(='<=' & Date(Today()))"}, [End Date] = {"$(='>=' & Date(Today()))"}, ID = {"=Sum({<[Start Date] = {""$(='<=' & Date(Today()))""}, [End Date] = {""$(='>=' & Date(Today()))""}>}FTE) <> 1"}>}FTE)

If(Sum(TOTAL <ID> {<[Start Date] = {"$(='<=' & Date(Today()))"}, [End Date] = {"$(='>=' & Date(Today()))"}>}FTE) <> 1, Sum({<[Start Date] = {"$(='<=' & Date(Today()))"}, [End Date] = {"$(='>=' & Date(Today()))"}>}FTE))

Re: Find available employees

akshada_samant — Mon, 05 Nov 2018 13:02:40 GMT

Thanks a lot sunny. Can you please help me with two more things.

1) When i am adding the said measure as a column i am getting null values also, how can i avoid the rows with null values.

2) I also want to show the total number of available resources:

Total resource count which will incorporate below conditions along with the above condition :

Count( { $< [Termination_date] = {">=$(=Date(Today(), 'MM/DD/YYYY'))"},

[Roll_Off_Date] = {">=$(=Date(Today(), 'MM/DD/YYYY'))"}, [Status]={'ACTIVE'}

>} [Name] )

Kindly help me to solve the above issues also.

Thanks in advance.

Regards,

Akshada

Re: Find available employees

sunny_talwar — Mon, 05 Nov 2018 13:15:38 GMT

Would you be able to share a sample where you can show the above two issues? And for the 2nd issue, it will help to know what the expected output is based on the sample provided

Re: Find available employees

akshada_samant — Wed, 14 Nov 2018 07:18:45 GMT

Hi Sunny,

The solution given by you works great but only thing is i need 3 more conditions in it. Kindly find the updated sample data below.

Sr_No	ID	Client	Project	Start Date	End Date	FTE %	Name	Termination Date	Roll off date	Status

1722	Jan-00	Client 2	Implementation	Apr-18	Jul-18	0.2	ABC	31/12/2099	31/12/2020	Active
1723	Jan-00	Client 3	Implementation	Feb-18	Apr-18	1	ABC	31/12/2099	31/12/2020	Active
1724	Jan-00	Client 4	Implementation	Jul-18	Mar-19	0.2	ABC	31/12/2099	31/12/2020	Active
2844	Jan-00	Client 3	Phase2	Apr-18	Dec-18	0.8	ABC	31/12/2099	31/12/2020	Active
232	Jan-00	Client 4	Internal Operations	Feb-16	Jun-19	0.1	DEF	31/12/2099	31/12/2020	Active
1958	Jan-00	Client 5	Phase 1	Nov-17	Apr-18	0.5	DEF	31/12/2099	31/12/2020	Active
1959	Jan-00	Client 6	Phase 1	Apr-18	Dec-18	0.5	DEF	31/12/2099	31/12/2020	Active
737	Jan-00	Client 7	Project 1	Jan-18	Feb-18	0.5	XYZ	31/12/2099	31/12/2020	Active
944	Jan-00	Client 8	Project 2	Feb-18	Oct-18	0.5	XYZ	31/12/2099	31/12/2020	Active
1378	Jan-00	Client 9	Project 3	Apr-15	Apr-20	0.25	XYZ	31/12/2099	31/12/2020	Active
1386	Jan-00	Client 10	Project 4	Aug-16	Apr-20	0.25	XYZ	31/12/2099	31/12/2020	Active

I have added 3 more fields - Termination date, roll-off date and status.

We need to add 3 more conditions to our set of conditions as below:

Termination date and roll off-date should be greater than today's date and status should be 'Active'.

I also need to show the total count of available resources with all the conditions. I need to show a table with columns - SAPID, Name, Total FTE%, Total available resources count, last column will show if the resource is Partially available or fully available.

Thanks in advance.

Re: Find available employees

akshada_samant — Thu, 15 Nov 2018 06:22:09 GMT

Hi,

Can anyone help me on this.

Regards,

Akshada