Sunday, April 12, 2020

FAQ Series - Creating Groups

Groups are a way to collect like items without creating an aggregate  - They can be static or dynamic, created manually, through a join or as the result of a calculation and spreadsheet users will find them as a useful replacement for a VLOOKUP -




Manual Grouping

Static groups can easily be created using the Create Groups functionality from the Dimension pane.  

Open the dimension and select "Create Group"




A window will open with several options on how to group members of the dimension

Search Option 

The search option can be used to select values with a common value or string - Use "Find All" then select "Group"




Then Rename the group for use later



Highlight and Select Option

Using the mouse, shift or ctrl keys highlight the members to be included then select Group



Add to existing group

When new members are to be added to an existing group use the highlight option and then "Add To" in the upper right corner and select the correct group from the dropdown




Create and Other Group

Any remaining members can be collected in an "Other" group using the check off box on the lower left




When done there will be a Dimension(group) will be added to the dimension pane which can be used alone or as part of a hierarchy





Create Group is easy to use for static (unchanging groups).  If the members of the groups are even infrequently changed other options may be a better choice

Use a Join to Create Groups

Often posters on the Forum want to recreate a Vlookup function from a spreadsheet calculator.  The approach uses a separate file that lists the dimension value and the corresponding group 

For example: Group customers alphabetically by the first letter of their last name.  

I created a simple excel file with the customer name (first and last) to match same dimension in our Tableau Superstore example, broke out the first and last name and then the first letter of the last name.





Then join to the Superstore data on Customer Name




and the Group has been added to the dimension pane




Grouping by joining a secondary file works when groups are changed infrequently or can be downloaded directly from another database.  I used a similar approach to grouping products into subcategories and categories along a product hierarchy taken from an ERP system.

Grouping using a Calculated field

When the grouping can be described using a calculation the groups can be even more dynamic.  The grouping by last name example can also be done using a calculation 


left( split([Customer Name],' ',2),1)




Grouping created by using a calculation are dynamic and change whenever the data are updated or changed

Regardless of how they are created, groups give you a way to categorize the dataset.  They can be used in hierarchies:





and can be filtered








or can be used in calculations




I hope this gives you more insights for groups and how they can be used 

The workbook and calculations used in the examples can be downloaded at :




Enjoy

Jim







No comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Post splash image

My Blog Has Moved