Determining full employment

I have a spreadsheet containing the employment details of 700 staff. These staff are charged wholly or partly to projects and I have to work out if they are fully funded between now and the 31st March 2021. For each record I have

Name

Project - marked 'Internal' if they have institutional funding as part of their arrangement

Project End Date

Date staff charge to project begins

Date staff charge to project ends

% charged to project

Date staff joined the company

Date staff left the company

These can be complicated, one of the staff in the main record has 36 entries. No method I try works for everyone. I've attached a sample dataset. Any suggestions are very welcome.

Richard

Sample records for pay query.xlsx

Datasets

Data Investigation

Accepted answers

rahul_Kumar_koshika

Hi @NotQuiteClueless --

I have built an alteryx workflow which will fulfill your requirement. The following are the detailed steps which i have taken in to consideration.

1.Excluded empty records, records of left employees and employees deployed on internal project

2. Wherever the charge end date is empty copied project end date

3. Generated missing months between charge start date and charge end date using generate record tool

4. Excluded records not falling between the period (01-08-2020 to 31-Mar-2021)

Detailed output break-up employee and project wise

Employee wise total summary

Also, please find the below attached input file shared by you and workflow built for your reference.

If this works, please mark it as a solution.

Solution for pay query.yxmd

Sample records for pay query.xlsx

All comments

AshleyL

Hi @NotQuiteClueless --

What is the criteria that you use to determine if the person is fully funded? Could you describe that a little more in depth?

Also, if you want someone to work with you more hands-on, Alteryx does have the Virtual Solution Center: https://community.alteryx.com/t5/Virtual-Solution-Center/tkb-p/vsc. You can schedule 30 minutes with a Solutions Consultant who can work through this with you on a call.

Best,

Ashley

NotQuiteClueless

Thanks for replying. The question I need to answer is whether between now (1st August) and 31st March 21 an individual is fully funded. I've inserted a table of data below for one person. They've been charged to four different projects: twice to RG83468 but one charging regime expires before the period I'm interested in. There are three charges to RG91452 of differing magnitudes operating at different times. They're fully charged to G101392 from August but that project ends at the end of December. So he's fully employed between March and December but then needs funding @ 100% after that. Hope that clarifies things

Project	Project end date	Split %	Charge start date	Charge end date	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar
G101764	31/12/2022	20	01/04/2020	31/05/2020	20	20
G101764	31/12/2022	40	01/06/2020	05/08/2020			40	40
G101392	31/12/2020	100	06/08/2020						100	100	100	100	100
RG83468	31/05/2020	25	01/12/2019	31/03/2020
RG83468	31/05/2020	30	01/04/2020	31/05/2020	30	30
RG91452	05/08/2020	75	01/12/2019	31/03/2020
RG91452	05/08/2020	50	01/04/2020	31/05/2020	50	50
RG91452	05/08/2020	60	01/06/2020	05/08/2020			60	60
					100	100	100	100	100	100	100	100	100	0	0	0

rahul_Kumar_koshika

Hi @NotQuiteClueless --

I have built an alteryx workflow which will fulfill your requirement. The following are the detailed steps which i have taken in to consideration.

1.Excluded empty records, records of left employees and employees deployed on internal project

2. Wherever the charge end date is empty copied project end date

3. Generated missing months between charge start date and charge end date using generate record tool

4. Excluded records not falling between the period (01-08-2020 to 31-Mar-2021)

Detailed output break-up employee and project wise

Employee wise total summary

Also, please find the below attached input file shared by you and workflow built for your reference.

If this works, please mark it as a solution.

Solution for pay query.yxmd

Sample records for pay query.xlsx

NotQuiteClueless

Most excellent! It's not perfect but that's down to my oversimplifying the problem. It's 95% of what I need and I can build on this to get my ideal solution

Many, many thanks

Richard

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703