Creating a CrossTable
In this article we'll walk through creating a sample CrossTable.
We have to start with an Excel data table. The reasoning behind that can be found in the data tables review article. If you would like to follow along you can use the data found in this example file.
Add the Chartnado Add-in to your worksheet. For instructions on how to do that refer to the Getting Chartnado article.
When you first open the Add-in, after you have signed in, you are presented with the Dashboard Builder:
This dashboard is only going to have one CrossTable in it. For a more in depth discussion of creating dashboards with more than one CrossTable you can find that in the getting started article for creating a dashboard.
Drag the CrossTable block from the left block menu to the Drop area, then choose to edit it: This will bring you to the CrossTable Builder. By default the visual for a CrossTable is a bar chart, which is what we want in this case.
Now we have to provide the data for this CrossTable. Click the 'Table' button. It should be pulsating indicating that a data table has not yet been selected: This will bring you to the Formula Builder. This is where all of the calculations are built in Chartnado. Now, you may be wondering why we would have to provide the data to the CrossTable as a formula. There could be several reasons for that:
- You want to apply a filter to the data
- You want to attach some What-If conditions to the data so that end users can adjust this CrossTable
For this example we don't want to do any of that but this is where it would be done if you did.
For now we'll simply associate the DataFrame for this CrossTable to the complete table of data coming from the Excel data table. For a more in depth discussion of what a DataFrame is, you can find that here.
Click on the DataTable block to select it and choose the name of the Excel data table from the menu on the right, in this case the Excel table name is simply Data: You'll notice that once the Data Table block is selected on the canvas the Inspector shows you a preview of the result of the expression. In this case the preview is a list of rows in the table. More details about how the Inspector works can be found here.
Now that we've chosen our table let's head back to the CrossTable Designer. To do that click on the CrossTable Designer button in the upper left corner:
Now that we have chosen our data source for this CrossTable, the available columns show up in the DataColumns list. The CrossTable interface is very similar to the pivot table interface, except for the Arguments section. We'll get to that in just a moment.
irst, drag Year to the Rows area, Color to the Columns area and Amount to the Values area. You should end up with one bar for each year and color:
However, that's not quite what we want. We were interested in taking this one step further and breaking down the intersection of year and color into countries. To do that, drag Country to the Arguments area.
So, this is what has happened:
- The rows were grouped on Year (on row for each of 2019 and 2020)
- For each of those groups, the resulting rows were grouped again (into columns) on Color (one for each of Blue, Green, Red and Yellow)
- A chart was built for each of the resulting groups. However, the chart only had one bar that represented all the rows for that group (e.g. 2019, Green)
- Since we wanted to further break down each of those groups into country, we dragged Country into the Arguments area to create a bar for each country
Now that we have our CrossTable built we can head back to the Dashboard Builder by clicking on the button in the upper left corner:
We now have a dashboard with a single CrossTable on it.
In this example we created a CrossTable using all of Rows, Columns and Arguments. One thing we would like to point out is that, generally speaking, when creating a CrossTable the only areas that are required to be populated for a given visual to work properly are Arguments and Values. More information can be found in the discussions of each of the visual types.