Determining full employment
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Solved! Go to Solution.
- Labels:
- Data Investigation
- Datasets
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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