Excel Forum

Counting on specific values in a pivot table for sequential week commencing dates

Jason Fabien

New Member

Members

Forum Posts: 1

Member Since:
February 24, 2018

Offline

February 24, 2018 - 1:25 am

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