Day four into the dashboard week, we were being given data from here.
The data was reshaped to some extent, however, the challenge with data this time is to normalise it with the inflation rate.
Chris discovered the inflation rate on this website. This is year-on-year inflation data that need further calculation.
I then converted the data into inflator to bring all the historical value to 2019 value. The challenge is the data I had was year-on-year inflation rate, so I needed to bring it to 2019 rate.
Firstly, I needed to calculate how many years difference between the year in discussion and 2019.
The second step is to use Generate Rows tool to generate years between target year and 2019.
The third step is to join data back together to get the Inflation Rate for the generated year
The last step is to create a field with number one and using the multi-row formula as below.
Below is the partial result for the conversion.
Now I can finally start visualizing it in Tableau.
Some interesting finds are
- Heal care cost account for 17% of total spending in 2019 as opposed to only 5% in 1959.
- People have changed their behaviour a lot when it comes to recreational spending. With photography ranked the top recreational spending in 1959, people now spend more on gambling as it third-largest recreational spending in 2019.
- Computers also played an important role in revolutionising the way people spend their money for fun.
- Hairdressing salons and personal grooming establishments have always been ranked top personal care spending in both 1987 and 2019, although the ranking saw some fluctuation during this period.
You can check out the Viz here for further information.