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
Thank you for the reply.
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.
1 of 1 people found this helpful
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.
#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'
#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
#3c Compute Using > Advanced
#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.
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)
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.
I will follow your thread hoping some of the experience users or Tableau workers can help you, and thereby learn a lot myself.
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.
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.
- 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.
Thanks for your help
1 of 1 people found this helpful
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.