Create Pivot or crosstab data from flat table – Consumer price index dashboard

In the previous section we built a simple dashboard using the CPI data.

In this part, we will use some pivot SQL queries to facilitate comparison of multiple categories on the same chart.

The charts require the data to be in certain format. It expects the first column to be the x-axis and the following columns as the y-axis values.

In case of multiple y-axis like stack bar or group column chart or multi line chart, we simply provide the values as columns

Image

 

If we want to display, say coffee and milk on the same line chart then our column1 should represent coffee and column2 should be milk and so on.

But as we now our data is not in this format. It is instead stacked one below another like this

Image

So we will use some SQL tricks to pivot the data

Image

We use the SQL as below

select *
from
(
select yr
,max(steak) steak
,max(bacon) bacon
,max(pork) pork
,max(gasoline) gas
,max(Margarine) Margarine
,max(Bread) Bread
,max(Butter) butter
,max(Eggs) eggs
,max(Beans) beans
,max(Milk) milk
,max(Coffee) coffee
,max(Sugar) sugar
,max(Potatoes) potatoes
,max(Rice) rice
,max(Flour) flour
from
(
select yr,item_category,
case when item_category='Steak' then average_annual_pricevalue
else 0
end steak
,
case when item_category='Bacon' then average_annual_pricevalue
else 0
end Bacon
,
case when item_category='Pork Chops' then average_annual_pricevalue
else 0
end pork
,
case when item_category='Gasoline' then average_annual_pricevalue
else 0
end gasoline
,
case when item_category='Margarine' then average_annual_pricevalue
else 0
end Margarine
,
case when item_category='Bread' then average_annual_pricevalue
else 0
end Bread
,
case when item_category='Butter' then average_annual_pricevalue
else 0
end Butter
,
case when item_category='Eggs' then average_annual_pricevalue
else 0
end Eggs
,
case when item_category='Beans' then average_annual_pricevalue
else 0
end Beans
,
case when item_category='Milk' then average_annual_pricevalue
else 0
end Milk
,
case when item_category='Coffee' then average_annual_pricevalue
else 0
end Coffee
,
case when item_category='Sugar' then average_annual_pricevalue
else 0
end Sugar
,
case when item_category='Potatoes' then average_annual_pricevalue
else 0
end Potatoes
,
case when item_category='Rice' then average_annual_pricevalue
else 0
end Rice
,
case when item_category='Flour' then average_annual_pricevalue
else 0
end Flour
from inflation
where item_category in
('Steak','Bacon','Pork Chops','Gasoline','Margarine','Bread',
'Butter','Eggs','Beans','Milk','Coffee','Sugar','Potatoes','Rice','Flour'
)
) as x
group by yr
order by yr asc
) as y

 

We use the case statement and combination of max and group functions to get the desired pivot table.

We then use selected columns to create a multi line chart.

select yr, steak,bacon,pork,gas
from
(
select yr,max(steak) steak,max(bacon) bacon,max(pork) pork,max(gasoline) gas,
max(Margarine) Margarine
,max(Bread) Bread
,max(Butter) butter
,max(Eggs) eggs
,max(Beans) beans
,max(Milk) milk
,max(Coffee) coffee
,max(Sugar) sugar
,max(Potatoes) potatoes
,max(Rice) rice
,max(Flour) flour
from
(
select yr,item_category,
case when item_category='Steak' then average_annual_pricevalue
else 0
end steak
,
case when item_category='Bacon' then average_annual_pricevalue
else 0
end Bacon
,
case when item_category='Pork Chops' then average_annual_pricevalue
else 0
end pork
,
case when item_category='Gasoline' then average_annual_pricevalue
else 0
end gasoline
,
case when item_category='Margarine' then average_annual_pricevalue
else 0
end Margarine
,
case when item_category='Bread' then average_annual_pricevalue
else 0
end Bread
,
case when item_category='Butter' then average_annual_pricevalue
else 0
end Butter
,
case when item_category='Eggs' then average_annual_pricevalue
else 0
end Eggs
,
case when item_category='Beans' then average_annual_pricevalue
else 0
end Beans
,
case when item_category='Milk' then average_annual_pricevalue
else 0
end Milk
,
case when item_category='Coffee' then average_annual_pricevalue
else 0
end Coffee
,
case when item_category='Sugar' then average_annual_pricevalue
else 0
end Sugar
,
case when item_category='Potatoes' then average_annual_pricevalue
else 0
end Potatoes
,
case when item_category='Rice' then average_annual_pricevalue
else 0
end Rice
,
case when item_category='Flour' then average_annual_pricevalue
else 0
end Flour
from inflation
where item_category in
('Steak','Bacon','Pork Chops','Gasoline','Margarine','Bread',
'Butter','Eggs','Beans','Milk','Coffee','Sugar','Potatoes','Rice','Flour'
)
) as x
group by yr
order by yr asc
) as y

The above SQL in the chart gives the below result

Image

Some quirks, we can provide the line colors and freeze them so that it does not change

Edit Chart properties and modify the following properties

colors=["#FFDB4D","#84C443","#49C4B4","#4955C4","#974AC4","#C44CA0","#C4525A"]

colorfreeze=true

key=["steak","bacon","pork","gas"]

title="steak - bacon - pork - gas"

Similarly you can duplicate and create another comparison between other item categories.

Here is the final dashboard : Consumer Price Index Dashboard

Here is the first part of this dashboard tutorial

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!