QAR Reconciler

This report is not supported for years beyond 2017/18.

In response to the increased complexity of the QAR calculation, we have produced a QAR reconciler report to help identify why differences occur between the published QAR and the Maytas achievement rates. Understanding the causes of differences can lead to better data quality and accuracy so that differences are reduced in the future.

To use the QAR reconciler you must first upload a QAR file:

1. Extract the contents of your QAR zip file. The file that needs to be imported is the 1718 QAR InYear (or full year) Apps (not APPS_Excluded).
2. Go to Management on the toolbar.

3. Go to Upload QAR | Upload QAR Apps file on the ribbon.

   

4. Browse to and select the QAR file to be imported and click Open.
5. The import will now take place.

Overview

Using the Report

The report is called QAR Reconciler and can be found in the Quality folder of the reports library.

The report has two parameters:

• QAR file – This lets you choose the imported QAR file to reconcile.

• Contract year – This lets you choose which contract year to reconcile. E.g. If you choose 2017/18, the report will attempt to reconcile overall cohorts from the QAR and Maytas which occur in 2017/18.

  

The report consists of three levels. The top level (shown above) shows a summary of the figures from Maytas and the QAR and their differences. Each of these figures drills down into the second level, which shows a list of the relevant learners and their data from Maytas and the QAR (note that there are more columns than shown on the screenshot below). Differences are highlighted red:

Clicking a learner name will go to the third level, which shows all relevant Maytas and QAR details for that learner, including a history of ILR export data, change history for important fields and a list of the probable causes of differences.

ILR Export History

The top-left table shows a selection of ILR export data that is relevant to the QAR calculation. This does not show ALL ILR export data for the learner – for example, if the completion status was changed from 1 to 2, the value of 2 may be included in 20 export files but the report may only show the earliest and latest exports with the completion status of 2. Essentially, the data in this table is what the Maytas achievement rates consider when calculating the results.

The export data can be used to show, for example, that a change of actual end date or completion status in the live data has never been exported, and therefore the ESFA cannot use it in their calculations meaning their results will be different.

It can also show that a change of value WAS exported but is different to the value in the QAR, which would mean either the export file was never submitted to the ESFA or it was submitted too late to be counted in the published QAR. There is now a flag on the ILR export screen to indicate an export has not been submitted, which the Maytas achievement rates will then take into account, meaning the results will match closer to the QAR. Details of this can be found in the Not Submitted Flag section.

Change History

This table shows the history of when values for completion status, actual end date and learner reference number were changed for the learner. This can be useful for checking whether a value was changed after the R14 deadline for a contract year, or for investigating values that have changed back and forth, resulting in unusual differences between live data and exported data.

Probable Causes of Discrepancies

This lists probable reasons for why there are differences between the Maytas achievement rates and the QAR. It is important to note that the listed causes are indicative only, and should be used as a guide for what to investigate. It is possible that there may be additional reasons for differences that cannot be directly derived from the available data.

These are the possible causes that can be derived, along with suggested ways to investigate:

• Actual end date of [dd/mm/yyyy] exported but not submitted to ESFA – This is shown when there is an actual end date included in an ILR export (shown in the ILR export table) but the QAR has no actual end date. Check the export date to see if the actual end date was included before the R14 deadline for the relevant contract year. If it was, was the ILR export submitted to the ESFA? If yes, it suggests that the ESFA have not yet picked up the actual end date in their published results. If no, we recommend identifying the relevant export(s) and ticking the Not submitted checkbox against them (see Not Submitted Flag section).
• Completion status of [value] exported but not submitted to ESFA – This is shown when there is completion status included in an ILR export (shown in the ILR export table) that is different to the completion status in the QAR. Check the export date to see if the completion status was included before the R14 deadline for the relevant contract year. If it was, was the ILR export submitted to the ESFA? If yes, it suggests that the ESFA have not yet picked up the completion status in their published results. If no, we recommend identifying the relevant export(s) and ticking the Not submitted checkbox against them (see Not Submitted Flag section).
• Maytas data last exported for [contract year] but QAR year aim submitted is [different contract year] – This is shown when the learner was last included in an ILR export for a particular contract year, but the year aim submitted on the QAR is a different year. This indicates that the ILR export(s) for that year may not have been submitted to the ESFA or used by them yet in the QAR. If the exports were not submitted, we recommend identifying the relevant export(s) and ticking the Not submitted checkbox against them (see Not Submitted Flag section).

• In-year QAR file does not count learner as an overdue planned break, but Maytas does as the learner has not restarted before end of [contract year] – This can occur when an in-year QAR for a contract year is being reconciled during the following contract year (e.g. if an in-year 2017/18 QAR is being reconciled in August 2018). This is because a planned break is time-sensitive and is considered overdue if the aim is not restarted by a certain point.

  For example, if a learner is on a valid planned break during 17/18 that would become overdue in 18/19 if not restarted, the 17/18 in-year QAR would consider it not overdue but Maytas would report it as overdue as of 1st Aug 2018. The final QAR for 17/18 would not have this issue in theory as it should count the learner as overdue and match the result in Maytas.

• Learner cannot be found in Maytas – This occurs when a learner reference number in the QAR cannot be matched to a learner record in Maytas. The two likely explanations for this are that the learner reference number has been changed in Maytas (in which case it may appear in the change history table), or that the learner record has been deleted in Maytas (e.g. if a learner requests a GDPR deletion).
• Learner cannot be found in QAR – This occurs when a Maytas learner counted in the overall cohort for the selected year cannot be matched to any QAR record. The most likely explanation for this is that the learner has never been submitted in an ILR export. If export records are present for the learner, check whether those exports were submitted to the ESFA. If not, we recommend identifying the relevant export(s) and ticking the Not submitted checkbox against them (see Not Submitted Flag section). Another possibility is that the learner reference number was changed in Maytas after being submitted, in which case it may appear in the change history table.

While the above reasons cannot account for every possible scenario that could cause a difference, they are the most common reasons for discrepancies. Many of them can be addressed simply by flagging relevant ILR exports as not having been submitted, which removes those exports from consideration in the QAR calculation.

Reconciliation Data

The achievement rate reports offer two different views of data: expected future published QAR results (the default) and expected current published QAR results. The QAR reconciler uses the latter, as it is designed specifically to match the expected published results as closely as possible by using exported data rather than live data in certain circumstances.

Not Submitted Flag

The ILR export submission screen now has a Not submitted checkbox which can be ticked to indicate that an export was not sent to the ESFA. The achievement rates will then take this into account and ignore any data from that export. Making use of this can significantly improve accuracy when trying to reconcile the Maytas achievement rates with published QAR files.

Analysing Differences

Starting Point

The most important figures on the first page of the QAR Reconciler are those in bold: QAR total, Maytas total and Matched rows. The two totals are a simple count of the number of rows from the QAR and Maytas where the overall cohort is in the contract year you've selected in the parameters. Matched rows is the number of rows where Maytas agrees with the QAR, so the closer it is to the QAR total, the better.

As a starting point for analysing differences, we recommend drilling down on the top-right figure: QAR total / Difference from matched rows. That figure essentially means there are x number of learners in the QAR where the overall cohort for the selected year is different in Maytas; i.e. most differences are likely to be in this count. When you drill into that number, the second level of the report is shown which lists the relevant learners with differences in data highlighted. The most important columns to look for differences in are:

• Completion status - Often this can be due to a planned break (6) where the learner has restarted or withdrawn, but where that hasn't been reported to the ESFA.
• Year Aim Submitted - This can have an impact on which year a learner is included in for the overall cohort, so if the overall cohort year is different between Maytas and the QAR then a difference in Year Aim Submitted could be the cause.
• Actual end date - A difference here may have an impact on which year the overall cohort falls in or possibly whether the learner is excluded from the overall cohort (e.g. if they haven't met the qualifying period).

To see greater detail for a learner, click their name to drilldown. This shows as much relevant data as is available, but the first place to check is the top-right box: Probable causes of discrepancies. This lists any scenarios identified from the data which could be the cause of differences (see above for details of these causes). The other tables here are intended to aid analysis.

Analysis Example

The following example shows how the report can be used to identify, explain and resolve a difference between Maytas and the QAR. It focuses on a learner, Jacob Matthews, with 2017/18 selected as the contract year.

To begin with, we drill down into the Difference from matched rows for the QAR total.

Jacob Matthews is listed here. Maytas has his Year Aim Submitted as 17/18 whereas the QAR says 16/17, and also the QAR is counting him as an overall leaver whereas Maytas says he is a continuing learner.

Both Maytas and the QAR agree that the completion status is 1 (continuing) and that no actual end date has been entered, which indicates that the learner should be continuing. Since the QAR counts him as an overall leaver with a completion status of 1 and no actual end date, this means the ESFA must be counting him as an overdue continuing learner.

The important difference here is Year Aim Submitted, because the data in the QAR suggests Jacob was last submitted in 16/17 and went through all of 17/18 without being included in a submission, which is why he is considered overdue continuing. The data in Maytas says he was submitted in 17/18 and so is still a valid continuing learner.

We drilldown into his details to view the ILR export history. The probable cause is, as expected, that the Maytas data was last exported for 17/18 but the QAR Year Aim Submitted is 16/17. The export table at the top-left shows that Jacob was last included in a 17/18 export on 01/03/2018. Since that is a significant amount of time ago, the ESFA should have picked that up in the QAR by now if the ILR export was submitted to them, so we have to conclude that either it was not submitted or Jacob's data in the export was not used.

The next step is to speak to the relevant data manager to try and figure out if the ILR export was submitted to the ESFA. If not, we go to the relevant ILR export in Maytas and tick the Not Submitted box (and do the same for any other exports known to have not been submitted), then re-run the QAR cache (Support tab, Ofsted Update button). Jacob's data should then match between Maytas and the QAR.

If the export WAS submitted to the ESFA, we would recommend contacting the ESFA to determine why the Year Aim Submitted is still listed as 16/17.