One of my favourite things about Qlik is the ease of taking different datasets and splicing them together. Its incredibly easy to do and allows you to enrich data and make comparisons and decisions that weren’t possible before.
To showcase this, I have taken a topic which is very much in the press at the moment, MP expenses and salary. I have taken datasets are taken directly from the UK Parliament open data area (https://www.parliament.uk) for salary and expenses from 10-11 to 19-20. These are then enriched with data from ONS (https://www.ons.gov.uk) to look at median salaries and deprivation indices for each constituency for 2019. The findings/opinions below are not politically driven or to specifically call out any party or MP.
With the Qlik QIX engine, you can drag and drop these datasets into the data manager and quickly link up the keys and begin creating some stunning visuals. To get all the data linked, I have quickly concatenated the expenses data for the last 10 years as well as doing the same for salary tables.
To start off I began to look at salary details. A lot of press talk about how MPs recently voted to increase their salary but not the public sector, however what analysis can we draw from these datasets when brought into Qlik Sense and when we are able to use historical data to view pay over time
As we would expect, basic salary spend has increased year on year, however the biggest change was in 2015, coinciding with the election that year and removal of the coalition with the Liberal Democrats.
Looking at the data in more detail and using different types of visuals to give more insight, this can be explained by a general trend of longer serving members of parliament (re-elected) having higher basic salaries.
This also makes a difference when we look at the average basic salary over time. In each election year the average salary per MP drops as new MPs win a seat, however as these MPs serve longer, obviously their wage rises.
Basic salary is only one part of the story though, as MPs can also be paid additional salaries for extra roles. This can range from being a committee chair or on a panel. There is also a London area living allowance that can be claimed. Having one of these extra roles and a LALP pushes the average up to 79.4k per MP (up 2.7% on 2018), with the top 10 highest salaries split equally between Labour and Conservative. These factors raise Charles Walker from the Broxbourne constituency the highest paid MP in the country at £97,813 for 2019
This is also true when looking at the whole dataset, with an average salary of £87,534 per year and increasing year on year.
As we can use the QIX engine in Qlik to enrich this data with other sources, we can then ask what that means when we compare these stats to the constituency of Broxbourne. Using ONS data and Open Source KML maps we can then visualise and compare these salaries to the median income per constituent and indices of deprivation
Broxbourne is a constituency that is much lower ranked on the indices of deprivation rankings and has a median wage of £25,731 in 2019. This does mean that the difference between the two salaries is 280%. As you can see from the visual below, this is not the largest pay gap in the East of England
The largest pay gap in this area is in Hemel Hempstead, where the median salary is only £15,402. In this constituency, the MP earns 416% more than the average person.
Nationally this is the second largest pay gap. When looking at the UK as a whole, the top 10 areas with the largest pay gaps are made up of 7 conservative, 2 Labour and 1 Plaid Cymru. These are dispersed geographically, apart from the joining of Berwick and Hexham to make most of the county of Northumberland in the top 10. The most concerning of the top 10 must however be Clacton. Clacton ranks as the 49th most deprived area of 500+ in the indices of deprivation.
When it comes to expenses, the yearly trend is increasing by about 5% per year. The majority of expenses are for staffing for each MP, then rent and travel.
What is surprising though is the average value per party, with the Green party and Caroline Lucas as the highest claiming average amount per party in 19/20, however this is an average and more to do with the fact other parties have some members claiming less, bringing the average down.
When you compare against the other peers in the same period, Caroline Lucas isn’t even the top 50. The top 10 average claiming MPs are listed below, with Jim Shannon and Ian Paisley of the DUP claiming over 2 million in the last 10 years. This is mostly explained by air travel and also hotels or rent for a second property in London. In Ian Paisleys case, an average of £24k on property rental is claimed, whereas Jim Shannon has claimed less with an average of £12k for hotels.
In conclusion, there are a few things in the dataset that stand out.
In terms of the salary dataset, the total amount paid for all MPs per year is rising, however the average salary per year does not share this pattern. From this we can use the data to see that this rise in the total spend is due to MPs that have been in position longer getting paid more. MPs can also claim for additional salary and a London allowance, however some of these London Allowances are claimed for constituencies outside the M25. There are also huge differences in pay for MPs to the median wage per constituency in some regions, most concerning in areas where the deprivation indices is high.
For expenses, there are no expenses that stand out, however it does make sense that MPs travelling from Northern Ireland spend more on travel, however hotels seem more cost effective than a second property.
If there’s anything else you would like me to explore, let me know!
