Aggregating data helps change the level of detail of a file or table – making it easier to combine data with other files and tables. In this article, am going to show you how to aggregate data in Tableau Prep to combine different tables together.
I will be using the Sample-Superstore data, whose level of detail is Order Id (each row in this data represents a single order or each product that has been ordered).
The second data is Target data whose level of detail is Region and Year (each row shows the Target sales for each Region and Year).
(You can generate this sample data in your own excel workbook).
Meaning for us to combine Sample-Superstore data with Target data (which are at different level of detail), we will need to aggregate one of these datasets, Sample-Superstore data for that case to make it less granular.
Connect the two data sets in Tableau Prep and add a clean step.
Add an aggregate step on the Orders table, after the clean step.
Drag Region and Order Date to Grouped Fields, and Sales to the Aggregated Fields area.
Next, lets change the Order Date level of detail from day to Year.
See how the data looks like by selecting the data grid.
Add a clean step.
On the clean step of the Target data convert the Year field (a date field) to a Year number.
Now, drag the clean step of Target data to the clean step of Orders data to join the two data sets (now at same level of detail).
Note, the applied join clause uses the field Region.
Add another join clause using Year and Order Date.
Add a clean step and hide one of the Region field and Year field.
(And as you can see above, our combined data has six fields (two which are hidden) and 16 rows).
Lastly, add an output step to export the data.
Therefore, by aggregating the Sample-Superstore data, we’re able to combine the two data sets, enabling one to use this data to track actual sales performance against the target Sales).
I hope this article was helpful to you. To receive more of the Tableau tips kindly join our mailing list by subscribing below.
Thank you for reading.