QAR Reconciliation Calculations in PICSWeb

Owned by Joe Robson (Unlicensed)
Apr 12, 2022
2 min read

This page explains the calculations used by PICSWeb to compare ESFA QAR reports to PICS data in the QAR Reconciliation process.

The Reconciliation Process

In order to compare the data between the two systems, the QAR Reconciler follows this process:

  • Load the QAR and various fields for each aim into a list.

  • Using the report options specified in the Create Reconciliation screen, we run a PICS QAR report and load the results into a list.

  • Loop through every aim in the QAR aim list, and try to find a matching aim in the PICS aim list. This matching is done using the Software Supplier Aim ID (known in PICS as the Aim RecGUID). If this fails, fallback to using LRN, Aim Reference, Programme, Start Date and Framework/Standard Code if applicable.

  • Any QAR aims with an expected end date after the report date are excluded, as these do not affect the current year and will not appear in the PICS report data.

  • Any aims which have achieved or ended after the report date are excluded, as the overall year will not affect the current year.

  • If there is no match, write the aim to the spreadsheet with status QAR not PICS. All PICS fields will contain -.

  • If there is a match, compare the two aims (see which fields we compare below) and if there are differences, write out the aim with status Data Difference and write the differing values in red. The first difference we find is also written to the Difference column - this is to help identify trends where the same field is different on lots of aims.

  • If there are no differences, write the aim to the spreadsheet with status No Difference.

  • Loop through the PICS aim list, and any aim not marked as completed (i.e. there is no match) is added to the spreadsheet with status PICS not QAR. Any QAR fields will contain -.

Once the reconciliation is complete, the report can be downloaded from the QAR Reconciliation list.

Fields Used in the Comparison

The following fields are compared during the reconciliation and any difference will cause a Data Difference status and the values to appear in red.

  • Include in Overall (Y/N)

  • Excluded (Y/N)

  • Expected End Year

  • Overall Year

  • Overall Achiever (Y/N)

  • Overall Completer (Y/N) - E&T reports only

  • Start Date

  • Planned End Date

  • End Date

  • Achievement Date

  • Traineeship Outcome Start Date - Traineeship reports only

  • Completion Status

  • Withdrawal Reason

  • Outcome

  • Overall Planned Break (Excluded)

  • Overdue Planned Break

  • Overall Transfer (Excluded)