Find available employees

[akshada_samant]

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

[sunny_talwar · Accepted solution]

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))

[akshada_samant]

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

[sunny_talwar]

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

[akshada_samant]

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. 

[akshada_samant]

Hi,

Can anyone help me on this.

Regards,

Akshada