Cross tab tool overview

1. Cross tab tool overview

Welcome to chapter three, where we're going to take a look at the cross tab tool, and how it works!

2. Unpivoting columns with the cross tab tool

Data often requires preparation and transformation before it can be presented. In some cases, we might need to break out the values in a column so that each of them has their own column. The cross tab tool helps us do just that. It is often called "unpivoting." The cross tab could be looked at as the opposite or the reverse application of the transpose tool. If something has been transposed, we can get it back with the cross tab.

3. Unpivoting columns with the cross tab tool

There are 3 aspects to the cross tab tool, and they are written here as the column headers on the first table.

4. Unpivoting columns with the cross tab tool

First, we have Group Data by these values in the blue column. This is much like key columns in Transpose, or like Groupby in SQL. They will be grouped together so there is one row per unique value.

5. Unpivoting columns with the cross tab tool

Second, we have Change Column Headers in the green column. A new column will be created for each unique value contained within the column selected. Only one column can be selected.

6. Unpivoting columns with the cross tab tool

Third, we have Values for New Columns in the orange column. This will determine what is found in the rows underneath those new column headers. The result is shown on the bottom right conceptually, but let's look at an example.

7. Unpivoting columns with the cross tab tool

There is a row for each of the unique values in category (our groupby column).

8. Unpivoting columns with the cross tab tool

There are new columns for each of the unique values in the Region column.

9. Unpivoting columns with the cross tab tool

Lastly, the sales values have been summed as the Values for our new columns, and totals are the same as the original table! Speaking of aggregation methods, let's discuss the options for aggregation using the Cross tab tool in this next section.

10. Cross tab aggregation methods

Depending on the data type you place in Values for New Columns, you may get 3 options if it is string, or you may get 10 if it is numeric! For numeric fields, there are many more options, and each of them can be very useful in a given situation, but the most common is concatenate for strings, and sum for numeric data. Let's step through a brief description for each.

11. Cross tab aggregation methods (numeric fields)

The available aggregation methods for numeric data are mostly intuitively named, such as Sum, Average, Count, First, and Last, which are highlighted here in the order they appear in the dropdown in Alteryx.

12. Cross tab aggregation methods (numeric fields)

To highlight a few less clear ones, let's look at the other four: Percent Row, Percent Column, Total Column, and Total Row. Percent row and percent column give a percent of total either horizontally or vertically. Total column and total row are much like row and column grand totals in excel.

13. Cross tab aggregation methods (string fields)

For string data, the Concatenate option allows the user to separate values based on a chosen character or set of characters (like split by delimiter in excel), or with First or Last they can take the first or last found value in the dataset.

14. Let's practice!

Now that you've stepped through the concept of "unpivoting" with the Cross Tab, let's test your knowledge!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.