8 Replies Latest reply on May 12, 2011 3:08 PM by Rama Kunaparaju

    Understanding the "index" function

    Rama Kunaparaju

      Attached is a workbook where i am trying to take only the top 5 customers within each customer segment by sales and then develop several views.


      I can do this easily by using index()



      But when i add several dimensions to the "level of detail" for tooltip purposes, obviously the index() changes and so is the results. I tried changing the table calculation of index()-compute using-advanced with several options, but could not get the desired results.


      For example: In sheet-1 i want to show the profits by customer in the descending order. This should consider only the top 5 customers by sales within any segment. What would be the index() formula?



        • 1. Re: Understanding the "index" function
          Joe Mako

          Your worksheet look a little strange to me, so I am not really sure what you are looking for.


          In your real workbook, for the additional dimensions that you place on the Level of Detail shelf, does each customer have a single value, or can they have more than one vale in each?


          The attached workbook goes off the idea that each customer has a single value in the other dimensions (I removed the dimensions that had multiple values per customer). If you added the other dimensions back into this view, this method would not work. I can provide more details on what is going on and the factor at play if this is what you are looking for.


          If you want dimensions on the level of detail shelf to have multiple values per customer, then you will have to explain how you want the situation handled, and the results you expect.


          If you look at the sheet that you provided, you will see that for 186 customer, there are over 1000 marks. This is because there is a mark for each distinct combination of the dimensions you have on the worksheet. You can also see that the "bars" go both up and down from the zero line. Is that what you want? or do you want one mark per customer that either goes up or down from the zero line?


          There are likely other factors that will effect the method used to get the results you are looking for, but what you have presented so far does not seem like a complete picture of your situation, so I am really not sure.


          As many addition details you can provide on your situation would be helpful. The more details you can provide about your situation, the more help I can provide.


          Table calculations in Tableau are not "one size fits all." I have found that each use needs to be built for the exact situation. If the situation is fully understood, then very powerful, dynamic, and flexible custom table calculations can be built, but with out details on the specific situation, I cannot say for sure how to get the result you are looking for.


          (also it is quite possible that I am over thinking the situation, in that case I am just not understanding what you are asking or looking for)

          • 2. Re: Understanding the "index" function
            Robert Morton

            Hi allAboutTableau,


            It sounds like your level of detail is affecting your table calculation. This is the default behavior, since each instance of a table calculation place on your visualization will have a default scope such as 'Table (Down)' or 'Table (Across)' depending on the nature of your visualization's layout.


            You may find that you can get the scope you want simply by setting "Compute using" to the field that matches the finest level of detail you wish to index. This setting can be accessed by right-clicking on an instance of your table calculation used in your visualization. Keep in mind that this has to be done for each place you use that table calculation on the visualization, including for filters and for any other calculated field which references the table calculation. If you edit the "Compute using" property of the table calculation within the formula window (the calculated field editor), you are simply changing the default for any new instances of that formula which you place on the viz.


            Does this help?


            • 3. Re: Understanding the "index" function
              Joe Mako

              Thank you Robert, that was a very clear description of the situation, and provided me the clarity to understand another situation. I combined your description here with the other situation, an now I believe I understand what is happening in both situations.


              The resolution you offer of setting the "Compute using" property does work in situations where every possible combination of the dimensions (that create a distinct data point) is available. If not all dimension combinations of interest are available, then the result can be achieved by padding the data.


              see The specified item was not found. for an example situation where padding the data resolved the complexity and unexpected results.


              If this technique is applied to this situation, a solution is possible with the level of detail shelf nearly as it is. I created a new sample data set from the view, just a select all, copy paste, and made the attached workbook. With some ETL, I made it so each customer had only one zip code. I allowed for each customer to be a part of more than one segment, have more than one supplier and more than one container.


              Robert, if there is a way to accomplish this filtered view in this attached workbook without padding the data, I would be highly interested in seeing an example of an alternate method.

              • 4. Re: Understanding the "index" function
                Robert Morton

                Hi Joe,

                I won't have a chance to dive into this deeply for a while, but a more elegant way to handle "padding" as all pair-wise combinations of two dimensions is to create a set from those two dimensions. It appears from your other posts that you're familiar with this approach, so perhaps I'm misunderstanding your question. I won't have a chance to reply, but I do hope this helps.


                • 5. Re: Understanding the "index" function
                  Joe Mako

                  You are correct Robert, the technique of using a table calc to pad the data is something I have made use of in the past, but I could not get it to work in this case. I think because I am trying to pad the data at one level, and perform the order along aggregation for the table calc at another.


                  I still have not made much use of Sets in Tableau (they are still a mystery to me), and I was unable to find a way to get a set to create all potential combinations (like a cross join), the set did give all existing combinations (like a select distinct). Is it possible for a set to give a result other than a concatenation of dimensions?


                  The workbook that I linked to in the other thread above did not work in my first two passes. but I now see what I missed, I did not pad on all dimension combinations that I needed to.


                  I look forward to the chance to hear more about using Sets in conjunction with table calcs to pad the data in a situation like this.

                  • 6. Re: Understanding the "index" function
                    Robert Morton

                    Hi Joe,


                    Quick comment: once you do understand sets & table calcs, you'll understand the mystery behind this How-To:  http://www.tableausoftware.com/support/knowledge-base/finding-top-n-within-category



                    • 7. Re: Understanding the "index" function
                      Joe Mako

                      Robert, thanks for the link, that is one of the best KB articles.


                      However, I do not see how the set provides an advantage or different result from a concatenation of the two dimensions with a calculated field. What I understand to be happening is Tableau performs the sort in the query sent to the data source, and then makes the panes after the sort, so in order to get a sort within the panes, the query sent to the data source needs that pane grouping to gain a sort within pane, and the concatenation of the dimensions is what allows the sort to happen at the desired level.


                      When I say Sets are a mystery to me, I mean I do not understand what they are best used for, and how they are better than a concatenation. What is the reason to use a Set instead of a concatenation? Also, I do not see how a Set or a concatenation could produce the kind of "cross join" preprocessing that I currently believe was needed to get the result that I produced in the above workbook.


                      Thank you Robert for the insight that you share, I learn something new about Tableau every day.

                      • 8. Re: Understanding the "index" function
                        Rama Kunaparaju

                        Joe and Robert,


                        When i change the dimensions in the level of detail shelf to attributes, they are then not used for table calcs. So a combination of making dimensions attributes and index--compute using-advanced solved my problem. Then i can use the level of detail attribute dimensions in the tooltip.


                        Thank you so much for your help. This is good stuff!