Consumer Price Index – Charts and Dashboard

In this article we will build the following consumer price index dashboard

The data used in this dashboard is from the Inflation Trends website

(source http://www.inflationtrends.com/ , compiled from various government sites such as http://bls.gov/data/#prices)

To begin with we have the following spreadsheet

Image

The raw excel file has multiple sheets but the data of our interests is in the columns 'B', 'C' and 'D' i.e. Item Category, Year and Avg Annual Price/value.

We just select the needed columns and press 'Ctrl+C' or right click and select 'Copy'

Image

 

Once the data is copied in clipboard, we will convert into a SQL table using the upload method

Login to InfoCaptor https://my.infocaptor.com/dash/getin.php?vi=cpi

From the launch pad, click on the Upload Excel to Table option

Image

 

Now, we go ahead and paste the excel data that we copied before

Image

 

1. Provide a name to the table. In this case we just give the name "inflation"

2. Paste the data in the text area

3. Click "Read and Show Preview" button

Once you click the button it shows the preview of how the data will be loaded below

Image

 

In the above preview, we just rename the "year" column to "yr" simply because sometimes the databases have a seeded year function and we just want to avoid confusion. Rest all looks good and we click on the 'Start upload Data'Image

 

Once the data is uploaded, it is ready for your analysis.

From the launchpad, we click on the 'Dashboard Designer/Editor'

Image

 

In the dashboard editor, we drag the grid and display the raw data that we just uploaded

Image

 

Right click on the grid and select 'Data Source'

Image

 

Image

 

1. Select 'Personal Cloud' connection from the connection drop down

2. Next, click on Show Tables and select 'inflation' table. This is the table we just uploaded our data into.

Once you select, it creates a SQL and adds it to the textarea.

Image

 

Click 'OK' and then it shows the data in the grid

Image

Now, that we have verified that our data we uploaded shows up in the dashboard, we can go ahead and build some charts

Let say we want to display a simple line chart displaying the trend for say 'Coffee' then we would simply write the below SQL

Image

In our select statement, we choose the yr and avg price columns and provide the item_category filter = 'Coffee'

Image

Now our grid shows only coffee related data.

We use our grid as our testing ground as we can see the raw data.

Next, we plot the above data into a line chart.

Drag the line chart to the canvas

Image

 

Right click on the 'Line Chart' and select 'Data Source'

We first reposition the line chart to make more room and extend the size because we have huge data set.

Image

We make sure the connection is 'Personal Cloud' and paste the SQL as below

NOTE: If you drag a new widget it will inherit the connection and SQL from the last updated widget and this saves time

Image

SQL

select yr,average_annual_pricevalue
from inflation
where item_category='Coffee'

Once you click 'OK', the data is immediately plotted on the line chart

Image

Now that the line chart shows up, we see some apparent issues with the display.

The x-axis shows all the year values jumbled up. We can fix this by slanting the year to a certain degree.

Image

Right click on the chart and select 'Edit Chart Properties'

Image

We locate the "text.angle" property and change it to 30 degrees.

Similarly we provide an appropriate title and also skip values on the X- axis so that it does not look clumpy

Image

Even after providing the angle to the x axis, they look overlapping.

For large datasets, it makes sense to show only certain intervals.

In the chart properties we modify the below properties

Image

 

title="Coffee"

xlabels.skipcount=8

Now we get nice and clean chart

Image

 

We can now squeeze the chart as we need to accommodate lot of other charts on the dashboard.

Image

 

Drag the right bottom corner and squeeze the size to the desired size.

You can change the color of the line chart to your own choice

Image

Click on the chart once to select it. (The selected widget/chart always has a blue border)

Next click on the Fill color on the top toolbar and select the color.

This finalizes our chart and we can now replicate the charts for other item categories as follows.

Image

 

Right click on the chart and select 'Duplicate'

Image

Now right click on the duplicated new chart and select 'Data Source'

Image

Just change item_category='Milk'

select yr,average_annual_pricevalue
from inflation
where item_category='Milk'

Image

Similarly we duplicate and add all the categories we want to see.

We also change the title attributes to match the item categories and yes, change the colors to distinguish them.

Image

Once we add all the categories, we can see our dashboard as below

Image

In the next part, we will see how to add pivot table and put multiple line charts together to get some context

Join InfoCaptor and Get Free Dashboard Software

Posted in dashboards, Excel, How-to | Tagged , , ,


Would you like to build your dashboard now?

If so, click to buy now, including 60 minutes of dashboard development for free!