Slide 1
Welcome to this video demonstration on how to use the post-deadline extracts for your organisation, to replicate published statistics for the Improving Access to Psychological Therapies data set, also known as IAPT.
Previously, providers received a PAVE report that flagged individual Pathway_IDs as Y/N for inclusion in counts. This report is no longer published, but providers can use the derived fields in post-deadline extracts for the same purpose. That is, to identify the referrals which have been included in published statistics.
Slide 2 – Obtaining the post-deadline extract for your organisation
The first step is to obtain the post-deadline extract for your organisation, from the Strategic Data Collection Service in the cloud (also known as SDCS Cloud). Further information on how to download this file can be found in the SDCS Cloud user guidance, within the subsection called 'Data Quality Reporting'. This is accessible from the NHS Digital website. There is also a link to the relevant page below.
Please note that the post-deadline extract file is available as an XML file. Once you have downloaded the post-deadline extract for the correct month, load the XML file into an appropriate programme to query the file. A number of programmes may be utilised such as SQL server or Microsoft Excel, amongst others. For demonstration purposes, we will use Microsoft Excel. However, the principles outlined are the same and can be applied to any programme used to query the post-deadline extract.
Slide 3 – Measure 192 Percentage_Recovery
In this video we will cover Measure 192, Percentage_Recovery. This is the proportion of referrals with a discharge date in the period that finished a course of treatment, where the service user moved to recovery. The denominator is a count of referrals finishing treatment in the period, minus those finishing a course of treatment who were not at case ness at initial assessment.
The first step is to identify the appropriate fields to filter from the post-deadline extract. This information can be obtained from the metadata for all published measures. In addition to this, to aid providers, a list of fields to filter from the post-deadline extract is included in the IAPT Guidance document.
Slide 4 – Measure 192 Percentage_Recovery Table: Columns to filter from post-deadline extract
Displayed on screen is a table which details the fields to filter from the post-deadline extract for M192. This measure uses fields only from the IDS101Referral table. The IAPT data set consists of multiple tables which are included in the post-deadline extract. For further information on the tables see the IAPT Data Model for Data Set Version 2.0.
As this measure is a percentage, we need to construct the numerator and denominator separately. We also need to calculate 2 parts to the denominator, to subtract those referrals finishing a course of treatment who were not at caseness at initial assessment.
As we mentioned earlier, the post-deadline extract can be queried using any number of programmes, such as SQL server, an Internet browser or Notepad. For the purposes of this demonstration, we will be using Microsoft Excel.
Microsoft Excel demonstration
Displayed on screen is a dummy post deadline extract. Note that the numerator/ denominator may be constructed in either order. The fields required to construct the denominator are: ServDischDate, CompletedTreatment_Flag, UsePathway_Flag and NotCaseness_Flag. We will begin by constructing Denominator 1. The first step is to apply a filter to the ServDischDate column. This date should be within the reporting period of the data. We will select May 2018.
Secondly, apply the filters to the additional columns. Identify the CompletedTreatment_Flag column and ensure only records with a value of ‘True’ are displayed. Repeat this step for the UsePathway_Flag column, leaving only those records with a value of ‘True’.
Next, we need to count the remaining records as these represent a count of referrals finishing treatment in the reporting period. To do this, navigate to column PathwayID and count the records in your post-deadline extract. This will be referred to as Denominator1. Make a note of this figure as we will come back to it later. In this example, we have 248 referrals.
As mentioned, we are using dummy data. The post-deadline extract obtained from SDCS cloud will contain all the records specific to your organisation.
To link the records back to locally submitted data, identify the column ServiceRequestId. This is a unique identifier for a Service Request, usually generated automatically by the local system upon recording a new Referral. This will help to identify the referrals which have been included in the published statistics.
Next, we will calculate the number of referrals finishing a course of treatment who were not at caseness at initial assessment. This will be referred to as Denominator2. To do this, clear all the filters applied to the post-deadline extract.
Again, the first step is to apply a filter to the ServDischDate column. This date should be within the reporting period of the data. And, we will select May 2018.
Secondly, apply the filters to the additional columns. Identify the NotCaseness_Flag column and ensure only records with a value of ‘True’ are displayed. Repeat this step for the UsePathway_Flag column, leaving only those records with a value of ‘True’.
Next, navigate to column PathwayID. Count the remaining records to calculate the number of referrals finishing a course of treatment who were not at caseness at initial assessment, which we will call Denominator2. Make a note of this figure.
Remember, we are using dummy data for this demonstration. Hence there is only one record shown.
Lastly, calculate the final denominator figure from your own post-deadline extract by subtracting Denominator2 from Denominator1.
Now that we’ve calculated the Denominator, we will calculate the numerator. The necessary fields to filter on for the numerator are: ServDischDate, Recovery_Flag and UsePathway_Flag.
The first step is to apply a filter to the ServDischDate column. This date should be within the reporting period of the data. Again, we will select May 2018.
Secondly, apply the filters to the additional columns. Identify the Recovery_Flag column and ensure only records with a value of ‘True’ are displayed. Repeat this step for the UsePathway_Flag column, leaving only those records with a value of ‘True’.
Finally, we need to count the remaining records as these represent the records included in the numerator for this measure. To do this, navigate to column PathwayID and count the records in your post-deadline extract. Make a note of this figure.
To reiterate, we are using dummy data for this demonstration. Hence there is only one record shown. The post-deadline extract obtained from SDCS cloud will contain more records, specific to your organisation.
To link the records back to locally submitted data, identify the column ServiceRequestId. This will help to identify the referrals which have been included in published statistics.
The last step is to calculate Percentage_Recovery using the numerator and final denominator figures calculated previously. Please note that the published percentages are based on unrounded values of the numerator and denominator, with the final percentage rounded to the nearest whole number.
The steps demonstrated in this video can be followed to reconstruct additional percentage measures within the published IAPT dataset. As mentioned, the numerator/ denominator may be constructed in either order, using any programme capable of querying the post-deadline extract XML file.
Slide 5
This brings us to the end of the video. Thank you for watching this video demonstration on how to use the post-deadline extracts to replicate published measures. We hope you found this useful.
For additional guidance on navigating the Improving Access to Psychological Therapies data set, please refer to the NHS Digital website and theIAPT v2.0 guidance document [Archive Content]. Thank you.