The second day of the Dashboard week saw us working with CPI and Wages data from the ABS.

ABS has a unique way to organise their data, whcih demands a data preparation process before you can start analysing the data. So here I will first talk about the Alteryx workflows to do this.

  • Transforming the ABS data

In ABS’ excel data files, the first page will be an index page which has all the column definitions. These infos will not be repeated in the actual data pages. So the first step will always be to get the column infos from this page. An example of the index page is shown below.

The following data pages have the history data up to the current day, but without the column infos. There are only column coding which need to be linked to the column infos from the index page. The typical data page is shown below.

Therefore the workflow in Alteryx to process these data will always have two part to deal with the index and data pages respectively and join the two parts into one at the end. An example of the workflow is shown below.

The two parts to deal with Index and Data pages seperately are seen here clearly.

  1. The Index Page: The transform will mainly be a text to column step to seperate column infos into different fields. Other steps are just cleaning the data, remove unnecessary fields and spaces, etc.
  2. The Data Pages: A transpose step is needed to transform all column data into row level in order to be able to do the join in the next step.
  • The dashboard design

First let’s have a look of the dashboard.

I should say the dashboard is more like an exploring dashboard. In it we can compare and try to find the corelations between the CPI and the Wages data by seeing them in different categories. Here is the summary of the dashboard design.

  1. Break up the comparison into two parts, first is by state and the second is by commodity group for CPI and industry for Wages.
  2. The commodity and the industry are at different level of categories and there is no way to link them togather. So the only way to show comparison is to place them next to each other,  giving the user of the dashboard the ability to use the different filters to find the closest categories to compare.
  3. On the CPI part, the preparation in Alteryx to get the Groups and sub groups hierarchy helped in the dashboard to provide the drill down like filter on CPI data.

Thanks for reading the blog. See you next time.

 

 

 

The Data School
Author: The Data School