Using the cross tab tool in a workflow
1. Using the cross tab tool in a workflow
Now that you’ve learned about unpivoting with the Cross Tab tool, it’s time to see it in action! In this video, we will step through our choices in each section of the Cross Tab configuration pane. It’s worth a brief walkthrough to show how the data in this example was prepared because the need for the Cross Tab tool often comes after data has been pivoted using a transpose tool. That’s exactly what’s happened here! The data was prepared with a Transpose, configured like this. Renaming and data type changes were made with Select. We need to unpivot it and get a wider dataset, with fewer rows, and category names across the header. We aren’t going to use subcategory, and we aren’t concerned with profit, since this is a sales report, but we need to keep the state and sales data. The final output should be a categorical sales report by state. State in the rows, and categories in the columns. First, let’s make sure our data types are what we would expect! Sometimes, even if a data type is clear in Excel, importing a file might change it back to a string. I’ll drag a select tool onto the canvas, and we will make sure that Metric Value is Double (a numeric data type). I’ll need to click run and look at the results. It is, so we can move to our next step. I’ll drag a Cross Tab tool after my select tool. While we’re here, I want to show a quick tip that saves manual effort in workflows. Since we won’t need any functionality from that select tool, I planned to delete it. I waited until the next step to show this. Often, we are deleting things that have tools after them. If you right-click on the Select tool and choose “Delete and connect around” you won’t have to redraw your lines between the tools! It’s a significant time saver! Now, we’ll focus on our Cross Tab tool. We know we must keep state information, so that will be in our groupby. However, if we use Category as our Change Column Headers, Metric Value as Value for New Columns, and Sum as our aggregation method and run that, we will have both Sales and Profit Values. It looks like this will be a two-step process. Let’s first pivot our metrics out, and then we can choose only sales. We will place State, Category, and Subcategory into groupby, Metric Name into Change Column Headers, and Metric Value into Values for New Columns. You may wonder, “I thought we weren’t going to use Subcategory?” You’re right! We will show how columns can be dropped with Cross Tab, so we want to keep it until our final step. The current configuration will create new columns for each unique value in Metric Name with the associated Values! We will choose Sum as our method for aggregating values and then click run. Now that the sales and profit data is unpivoted, we can add another Cross Tab to achieve our goal. We will groupby state, change column headers to category, and values for new columns to sales. This will drop subcategory and profit from the dataset, but that’s good for our purposes! Lastly, we will again choose Sum as our aggregation method and then click run. After running it, we can see a categorical sales report by state. Perfect! Now it’s your turn! Let’s get into Alteryx Desktop and start Cross Tabbing!2. Let's practice!
Now it’s your turn! Let’s get into Alteryx Desktop and start Cross Tabbing!Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.