8 Replies Latest reply on Sep 21, 2012 5:09 AM by Jonathan Drummey

# How to create Dynamic Sets from Rank [Index()]

Hi Guys,

I am trying to create five dynamic sets for a customer segmentation I am working on: A Customers; B Customers; C Customers; D Customers; E Customers.

I have already created a Rank [using Index()] and a calculated field that seperates customers into these 5 segments (quintiles): Top 20% go into A, next 20% B etc. I can use a filter to only show the segment I want: A or C etc . I can also MANUALLY create a set for each segment but what I am trying to do is make these sets DYNAMIC so that as customers move up in RANK (sales or profit) they will automatically be added to the correct set based on whether they fall into the top 20% or next 20% and so on.

I have also tried to use a formula in the "Set" to pull just the customers I want into the set but the fields I am tyring to use are calculated fields and I get "field invalid" errors when trying to create my formula. (Think its because they calculated fields)

I have attached the workbook I am using, you will see the different customers and segments by rank (colour-coded as well).

Would anyone know how to create dynamic sets based on the Rank?

• ###### 1. Re: How to create Dynamic Sets from Rank [Index()]

Hi Steven, I would like to look at your workbook, but cannot open it. It would be easier for me if the file was attached as a packaged workbook.

PS! The last few days I have tried hard to find a ranking solution using the index() function, and wonder if I have learned enough to help you, see my question & solution here: http://community.tableau.com/thread/120063

• ###### 2. Re: How to create Dynamic Sets from Rank [Index()]

Hi Johan,

I have re-uploaded the "packaged" workbook, sorry about that. I have also had a look through your work that you have done, very nice stuff and seems that you have put a lot of effort into it and you are certainly further ahead at understanding Index() than I am.

I will take a look at your workbooks and see if I am ale to come up with something, you never know what a fresh perspective could hold.

• ###### 3. Re: How to create Dynamic Sets from Rank [Index()]

Hi Steven,

The changes in attached file is limited to coloring a second measure, so you can compare color ranking of two measures side by side with color indicating ranking group A,B,C,D,E.

My Changes

#1. Changed formula [Client Sales Categories] to be independent of Rank column:

IF INDEX()/WINDOW_MAX(INDEX()) < 0.20 THEN 'A Client'
ELSEIF INDEX()/WINDOW_MAX(INDEX()) < 0.40 THEN 'B Client'
ELSEIF INDEX()/WINDOW_MAX(INDEX()) < 0.60 THEN 'C Client'
ELSEIF INDEX()/WINDOW_MAX(INDEX()) < 0.80 THEN 'D Client'
ELSE 'E Client'
END

#2 Added Sum(Profit) to COLUMN Shelf

#3 Changed Marks to Multiple Mark Types

#3a Browse to Sum(Profit) Mark

#3b Right click [Client Sales Categories] > Edit Table Calculation

#3d Order Along Field: Profit Sum Descending

#3e OK, OK

I hope this help you coming closer to what you want to do.

I learned something myself from your quin-tile formula and will sooner or later likely use something similar myself.

1 of 1 people found this helpful
• ###### 4. Re: How to create Dynamic Sets from Rank [Index()]

Hi Johan,

Thank you for your efforts: I like what you did with points number 1: Making the Clietn Sales Categories independant from the rank column.

However, I am still stuck as to HOW to dynamically create a set, based on a formula that only includes Client Sales Category A, or B etc.

Its the By Formula part that I am not sure of, tried a couple combinations but keeping getting "invalid" error messages.

Maybe someone else out there can help? (Thanks for trying Johan)

• ###### 5. Re: How to create Dynamic Sets from Rank [Index()]

Hi Steven,

Please notice that various functions are missing in sets that are available in calculated fields, such as all functions with prefix WINDOWS% and RUNNING%, FIRST, LAST, PREVIOUS_VALUE, and INDEX().

I am quite confident this is the reason you get a message that [Client Sales Categories] is invalid. Therefore current formula with INDEX() and WINDOW_MAX() cannot be used.

• ###### 6. Re: How to create Dynamic Sets from Rank [Index()]

Hi Steven,

You can't create a set based on a table calc (the functions that Johan had mentioned are all table calculations). However, pretty much everything that can be done with sets can be done with other calculated fields, so you can use the results of the Client Sales Categories in other calcs to get the results you desire.

Jonathan

• ###### 7. Re: How to create Dynamic Sets from Rank [Index()]

Hi Jonathan,

Thanks for the insights. I dug around a little and realised that a better solution would be "Groups", that way I could compare the different groups against other measures on the same viz instead of being stuck analyzing 1 set at a time.

However, I still don't know how to achieve what I am looking for 'dynamically' without having to have all the customers and the Rank (Index) on the viz.

Example:

- Create a calculated field that automatically assigns customers into groups based on whether or not they fall into the top 20%; next 20% etc based on Profit or Sales or Orders WITHOUT having to add customers and rank to level of detail or a shelf.

For now, I have to create a view with customers by rank and then manually highlight the top 20% and group into A, then highlight next 20%, group into B etc. Then every day we have NEW customers coming into the datasource so I would have to weekly or bi-weekly re-do the ranking and grouping manually.

I am convinced there must be a way to do it without having to resort to SQL in the datasource.

Still hoping someone knows how to do this and could provide an example as my calculated field abilities are not up to scratch yet.

• ###### 8. Re: How to create Dynamic Sets from Rank [Index()]

Hi Steven,

Unfortunately, Group assignment is not dynamic so that won't meet your needs either. I think we need to go back to original purpose - you've stated that you are trying to do customer segmentation, but you haven't specified what your goal is with the customer segmentation - can you give more detail about what the desired view or views are? That would help me or someone else here on the forums get more specific with a solution.

Given that ranking customers in Tableau requires access to the Customer level of detail, those fields will have to be in the level of detail in the view, but not necessarily displayed. The Level of Detail Shelf can be used to add those fields to the view, and then there are various techniques that can be applied via calculated fields so that only the desired results (perhaps only a few marks, or just one number) are shown. Also note that the results of calculations like INDEX() that use a certain sort order and aggregation can be nested inside other table calculations that use a different sort order and different aggregation.  For example, you could set up a line chart of the sum of profit of only the A segment. There are some limitations about what Tableau can do, so having a better sense of what your requirements are will enable us to craft a solution that meets your needs.

Jonathan

1 of 1 people found this helpful