Using multiple columns of data in Chart Builder visualizations
Chart Builder is a quick and easy tool for creating visualizations of data on the fly, but there is one thing that isn't easy to do with it: include data from more than one column in your graph. This limitation can be a real problem if, say, you want to look at both the high and low temperatures on the days when Bigfoot was sighted. Or if you want to have a graph with gender, attack type, and fatality in shark attacks so you can see if there is any correlation between them. Though you can easily run the queries to display the data, you can't obviously render it all at the same time in Chart Builder. However, though it is a bit tricky and requires the use of UNPIVOT, you can build visualizations in Chart Builder that include data from more than one column in a query.
In this article we'll use the Project Monsters Among Us to show how to include two related fields in a visualization, and Analysis of shark attacks by region and species to do a little fancier combination of multiple columns of unrelated data into one visualization.
How to show data from two related fields in a Chart Builder visualization
There is a query called High and low temperatures on the dates of Bigfoot sightings in the Monsters project that returns a simple table with three columns:
 |
Click on the Chart icon above the results to build a quick visualization from the query results. Set the X axis to date, the Y axis to temperature_low, and you have a visualization, but where do you put temperature_high?:
 |
Looking at the data, the solution is to put both the high temp and the low temp values in the same column and call it temperature, and to have another column called temp_value that would indicate whether the temp shown is a high temp or a low temp for the day. Fortunately, this kind of data reorganization where columns get collapsed into rows is what the SQL UNPIVOT command does. Here is the original query rewritten to use UNPIVOT to collapse the high and low temp columns into one column, and the resulting table:
 |
Select Chart to use Chart Builder on the results of the query, set the marks to Circle, the X axis to date, the Y axis to temperature, the color to temp_type, resize the chart toto 640 X 700, and you'll have this visualization:
 |
Combining multiple columns of unrelated data into one visualization
In this example we have a query in the project Analysis of shark attacks by region and species that returns dates, type of attack, gender of the victim, and whether the attack was fatal or not:
 |
To get a quick visualization of it select Chart, setCircle for marks,year for the X axis (you might have to open the options and set the type to Ordinal), COUNT (*) for the Y axis, and Gender for the color. Once it's been resized you get this chart:
 |
As in the last example, there's no way to include attack-type or fatality data. However, a redo of the original query with UNPIVOT combines all the data into one column ready for Chart Builder:
 |
Note: Even though there is a warning that only the first 10,000 rows of the results are displayed, when we chart the query with chart Builder, all the data is used in the visualization.
The chart from the query is built the same as before. SetCircle for marks,year for the X axis (you might have to open the options and set the type to Ordinal), COUNT (*) for the Y axis. Set Type for the color. Once it's been resized you get this chart::
 |
If you want to try unpivoting some queries on your own and charting them, there are a couple more--Provocation and gender in shark attacks, and Provocation and fatality in shark attacks--saved on the project that you can use.
Additional information about UNPIVOT can be found in our SQL documentation.