Hi all
Very new to this, this forum is an excellent resource.
I have a data set that is pulled from a SharePoint intranet site providing data on recruitment activity in the business. The recruitment manager currently manually updates a simple KPI spreadsheet using that data. I've been asked to help automate the process as much as possible.
Using a pivot table I can get most the required elements, but I am struggling on two things:
1) Dates.
The KPI spreadsheet has rows for each week. For each week the KPI report gives the total number of applicants who had one or more of the activities occur in that week.
KPI spreadsheet
W/C | Apps. | RAF | RAF % | Duplicates | Screened | Screened % | App:Screen | Pass Screen | Passed % | I'views Booked | I'views as % of Apps | No Shows | No Show % | I'views Passed | Pass Rate | Passed Vs Attended | Screen:Interview | TTWF | Int:TTWF |
14/08/2017 | 259 | 3 | 1.2% | 41 | 114 | 52.3% | 2.82 | 52 | 45.6% | 42 | 16.2% | 17 | 40.5% | 16 | 38.1% | 64.0% | 3.50 | 18 | 72.4 |
21/08/2017 | 247 | 5 | 2.0% | 38 | 116 | 55.5% | 2.21 | 45 | 38.8% | 55 | 22.3% | 33 | 60.0% | 15 | 27.3% | 68.2% | 5.32 | 15 | 68.3 |
28/08/2017 | 342 | 3 | 0.9% | 54 | 104 | 36.1% | 2.34 | 43 | 41.3% | 31 | 9.1% | 13 | 41.9% | 13 | 41.9% | 72.2% | 4.45 | 4 | 46.5 |
04/09/2017 | 337 | 6 | 1.8% | 61 | 124 | 44.9% | 1.73 | 57 | 46.0% | 50 | 14.8% | 18 | 36.0% | 23 | 46.0% | 71.9% | 5.62 | 8 | 84.8 |
11/09/2017 | 418 | 3 | 0.7% | 82 | 144 | 42.9% | 2.33 | 79 | 54.9% | 62 | 14.8% | 32 | 51.6% | 17 | 27.4% | 56.7% | 4.93 | 4 | 62.5 |
18/09/2017 | 369 | 5 | 1.4% | 46 | 134 | 41.5% | 2.59 | 56 | 41.8% | 80 | 21.7% | 41 | 51.3% | 22 | 27.5% | 56.4% | 5.16 | 11 | 61.2 |
The source data is a flat file
FullName | RecruitingBranch | JobSource | ApplicationDate | ScreeningDate | ScreeningOutcome | InterviewDate | InterviewOutcome | TTWFDate |
RAF | 08/01/2018 | 11/01/2018 | Failed: No Vacancies | |||||
JobBoard | 03/11/2017 | 06/11/2017 | Proceed | 12/11/2017 | Passed | |||
RAF | 31/12/2017 | Awaiting | ||||||
JobBoard | 29/12/2017 | 02/01/2018 | Proceed | 05/01/2018 | Failed | |||
JobBoard | 04/10/2017 | Awaiting |
So, in the "App" column on KPI I need to count the number dates in "Application date" in the source data where the date falls in the KPI week commencing date (e.g. WC 14/08/2017 would count any row with an application date between 14/08/17 and 20/08/17, WC 21/08/17 would count any row with an application date between 21/08/17 and 27/08/17, and so on).
The same applies for column "Screened" on KPI, I need to count the number of dates in "ScreeningDate" in the source for each WC row; "I'views booked" count the number of dates in "InterviewDate" in the source for each WC row, and so on.
Is this possible?
2) Count specific items in a field.
Still working on a row for each week, in KPI for "RAF" I need to count the number of rows in that week that have "RAF" in JobSource in the source code.
In "Screen Pass" in KPI the count is for "Proceed" in the source data
In "I'views Passed" in KPI the count is for "Passed" in the source data
Perhaps pivot tables is not the way to go? Looking forward to any suggestions you can make.
Thanks
Hi Jason
Welcome to the Forum.
I would suggest you use the WEEKNUM() to identify the week number of the dates in both your KPI and Data sheets.
You can then use the COUNTIFS() function to match the week number of both sheets and get the results.
Hope this helps.
Sunny