How to calculate the measures

A method of calculating the traveline data measures

Several people have asked for advice on how to calculate the measures. This worked example is intended to provide clarity and a simple method of setting up a spreadsheet or database to do the calculations. If you automate the calculation all you do is merge the source records, set the dates and print off the results.

County	Identifier	Category	Date received	Date registered	Date processed	Date on Journey Planner	Date starts
PATS number can be used	the registration or route number	whether to be reported - see Defined Minimum	the date received by the transport authority - date stamp	this is only useful to show if there has been some delay in submitting the registration	the date processed that all ambiguities have been resolved and the data is ready to submit	this can be an estimated date based on knowledge of the processing schedule	the date the bus service change is due to take effect

If each transport authority keeps similar records they can be gathered and merged into one file whenever analysis is to take place. There is no need to concern the authority with what the date period for the analysis is, the file for the whole year can be sent, for the appropriately dated records to be extracted later. With apologies to Northumberland, Nexus and Durham, the following table shows ficticious data received from three authorities.

B	C	D	E	F	G	H	I
County	Identifier	Category	Date received	Date registered	Date processed	Date on Journey Planner	Date starts
310	PC23456	A	5.12.2005	2.12.2005	19.12.2005	6.1.2006	29.1.2006
310	PC23556	C	6.12.2005	2.12.2005	19.12.2005	6.1.2006	29.1.2006
310	PC23656	C	18.12.2005	2.12.2005	20.12.2005	6.1.2006	29.1.2006
310	PC23756	A	18.12.2005	2.12.2005	20.12.2005	6.1.2006	29.1.2006
310	PC23856	A	19.12.2005	2.12.2005	9.1.2006	20.1.2006	29.1.2006
310	PC23956	A	12.1.2006	2.12.2005	10.1.2006	20.1.2006	29.1.2006
310	PC24456	A	12.1.2006	2.12.2005	11.1.2006	3.2.2006	29.1.2006
410	PC25456	A	19.12.2005	16.12.2005	2.1.2006	20.1.2006	12.2.2006
410	PC26456	A	20.12.2005	16.12.2005	2.1.2006	20.1.2006	12.2.2006
410	PC27456	A	1.1.2006	16.12.2005	3.1.2006	20.1.2006	12.2.2006
410	PC28456	A	1.1.2006	16.12.2005	3.1.2006	20.1.2006	12.2.2006
410	PC29456	C	2.1.2006	16.12.2005	23.1.2006	3.2.2006	12.2.2006
410	PB43456	C	26.1.2006	16.12.2005	24.1.2006	3.2.2006	12.2.2006
410	PB53456	A	26.1.2006	16.12.2005	25.1.2006	17.2.2006	12.2.2006
130	PB63456	A	2.1.2006	30.12.2005	16.1.2006	3.2.2006	26.2.2006
130	PB73456	A	3.1.2006	30.12.2005	16.1.2006	3.2.2006	26.2.2006

For each registration you can establish by date arithmetic

B	C	D	J	K	L	M
County	Identifier	Category	56 days notice	28 days notice	Timely	By start date
			If(I-E>50,"y",)	If(I-E>28,"y",)	IF(I-H>14,"y",)	IF(I-H>0,"y",)
310	PC23456	A	y	y	y	y
310	PC23556	C	y	y	y	y
310	PC23656	C		y	y	y
310	PC23756	A		y	y	y
310	PC23856	A		y		y
310	PC23956	A				y
310	PC24456	A
410	PC25456	A	y	y	y	y
410	PC26456	A	y	y	y	y
410	PC27456	A		y	y	y
410	PC28456	A		y	y	y
410	PC29456	C		y		y
410	PB43456	C				y
410	PB53456	A
130	PB63456	A	y	y	y	y
130	PB73456	A	y	y	y	y

Some of the data measures look for two conditions to be true. Therefore a further calculation can establish:

B	C	D	N	O	P	Q
County	Identifier	Category	56 days & timely	28 days & timely	56 days by start	28 days by start
			if(J="y"&L="y","y",)	if(K="y"&L="y","y",)	if(J="y"&M="y","y",)	if(K="y"&M="y","y",)
310	PC23456	A	y	y	y	y
310	PC23556	C	y	y	y	y
310	PC23656	C		y		y
310	PC23756	A		y		y
310	PC23856	A				y
310	PC23956	A
310	PC24456	A
410	PC25456	A	y	y	y	y
410	PC26456	A	y	y	y	y
410	PC27456	A		y		y
410	PC28456	A		y		y
410	PC29456	C				y
410	PB43456	C
410	PB53456	A
130	PB63456	A	y	y	y	y
130	PB73456	A	y	y	y	y

It is then possible to calculate a table with results for each authority. You can either sort the file on Column G to delete all registrations not processed in the monitoring period or you can build the date range into the calculations. In our example the calculation is of processing from 20 December 2005 to 19 February 2006, thus excluding the first two records.

Category	A
	56&timely	28&timely	total timely	56&start	28&start	total at start	Total
Authority
130	2	2	2	2	2	2	2
310	0	1	1	0	2	3	4
410	2	4	4	2	4	4	5
Region	4	7	7	4	8	9	11


Category	C
	56&timely	28&timely	total timely	56&start	28&start	total at start	Total
Authority
130	0	0	0	0	0	0	0
310	0	1	1	0	1	1	1
410	0	0	0	0	1	2	2
Region	0	1	1	0	2	3	3


Category	All
	56&timely	28&timely	total timely	56&start	28&start	total at start	Total
Authority
130	2	2	2	2	2	2	2
310	0	2	2	0	3	4	5
410	2	4	4	2	5	6	7
Region	4	8	8	4	10	12	14

Some of the difficulty calculating the measures may be getting the percentages right. Therefore "What is the appropriate total to use to calculate the various percentages?"

Which total?	56&timely	28&timely	total timely	56&start	28&start	total at start
Authority	Authority total processed of appropriate category, that had been received with 56 days notice	Authority total processed of appropriate category, that had been received with 28 days notice	Authority total processed of appropriate category	Authority total processed of appropriate category, that had been received with 56 days notice	Authority total processed of appropriate category, that had been received with 28 days notice	Authority total processed of appropriate category
Region	Region total processed of the appropriate category, that had been received with 56 days notice	Region total processed of the appropriate category, that had been received with 28 days notice	Region total processed of the appropriate category	Region total processed of the appropriate category, that had been received with 56 days notice	Region total processed of the appropriate category, that had been received with 28 days notice	Region total processed of the appropriate category

In terms of our example these totals for Category A equate with the following numbers:

Category	A
Relevant Totals
	all 56 days	all 28 days	all regs	all 56 days	all 28 days	all regs
Authority
130	2	2	2	2	2	2
310	0	2	4	0	2	4
410	2	4	5	2	4	5
Region	4	8	11	4	8	11

The actual values divided by the total and multiplied by 100 give the following percentage figures:

Category	A
Percentages
	56&timely	28&timely	total timely	56&start	28&start	total at start	Total
Authority
130%	100%	100%	100%	100%	100%	100%
310%	0	50%	25%	0	100%	75%
410%	100%	100%	80%	100%	100%	80%
Region	100%	87.5%	64%	100%	100%	82%

In the end you return the percentage figures from above which equate with the following target figures:

Category	A
Targets
	56&timely	28&timely	total timely	56&start	28&start	total at start	Total

Region	100%	75%	50%		80%	60%