Get Ready For Your Tableau Certification
  • Bernard

Aggregating Data in Tableau Prep


Tableau prep aggregate

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).

sample data

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).

sample data

(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.

connect data and add a clean up step in Tableau prep

Add an aggregate step on the Orders table, after the clean step.

adding an aggregate step in Tableau prep

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.

changing level of detail of a field in Tableau prep

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).

joining two tables in Tableau prep

Note, the applied join clause uses the field Region.

Add another join clause using Year and Order Date.

adding join clauses in Tableau Prep

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.

exporting data in Tableau prep

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.

LEAD MAGNET - 2.png
FREE INTRO COURSE -2.png