9 Replies Latest reply on Aug 6, 2018 12:10 PM by JANIT KAPIL

    Index Table Calculations

    Nathan Schofield

      Hello,

       

      I an using INDEX() because I want to filter by the top x in my worksheet. E.g. the top 2 names per month (below)

      Screen Shot 2013-03-05 at 2.37.46 PM.png

      My problem is that I don't understand how the advanced 'compute using' works to get Index to do what I want. I have attached an example and have screenshots here also. In this instance I have managed to get it to work by trial and error. In the attached example I would now be able to filter to the top 2 or 3 per month. What I am looking for is a good explanation of how the following dialog boxes work (e.g. 'Compute using', 'order along', 'at the level', 'restarting every'). Ideally if someone could explain in plain english how this works for my example this would be great.

      The example I have provided is simplified. I am working with much more data and tend to have many more variables involved, so it appears to get more and more complicated.

       

      Screen Shot 2013-03-05 at 2.28.10 PM.png

      Screen Shot 2013-03-05 at 2.27.55 PM.png

      Workbook also attached.

       

      Nathan

        • 1. Re: Index Table Calculations
          Dan Murray

          Nathan;

           

          Tableau calculations--and especially "windowing" table calculations--are probably one of the most confusing techniques to wrap your brain around.  I'll give it a try. There are two levels thought required to solve your problem:

           

          1. How to achieve the right sort order (considering the hierarchy you've defined).

          2. How to get the index "window" table calculation to consider the combination of dimensions from your hierarchy.

           

          You need to create a row aggregate for Tableau to sort upon that considers the game/month/name together.  After creating that set you must then place the set pill into a position on the row shelf that allows Tableau to correctly compute the sort.  The attached workbook shows examples of how different placements change the sorting and it will be obvious to you what is going on when you look at that.

           

          As long as the set is placed next to the "name" field (left or right of) the index that you want to create should work. If the set placement isn't right the index won't work as desired.

           

          3. Defining the index

           

          Remember the structure of your view effects how the index behaves because INDEX() is a windowing table calculation function.  The definition of the index has (3) parts:

           

          a. Define the fields to consider in the calculation in the COMPUTE USING DEFINITION BOX (in your example you want to include all of the fields that make up the set). GAME/MONTH/NAME

          b. At the level:  This is the level of detail for which you want to compute the index value. In your example you express the index at the NAME level of detail.

          c. Restarting every: Defines when you want reset the index calculation. In your view the level of the partition (window) is at the MONTH.  So, you need to reset the index counter so that at the beginning of each month (as expressed in the view) you restart the index.

           

          Set and Index Question.png

           

          4. Add the index to the filter shelf by CTRL dragging it from the row shelf when you have it working properly. Then make the index a quick filter and set it for whatever number of ranking level you want to see.  If you don't like multi-select filters, then change the index on the filter shelf to "continuous" and then make your quick filter. This will result in a sliding-type quick filter that I personally prefer on large index rank lists.

           

          I realize that Window Table calculations can be confusing.  Sometimes a little trial and error is required to get things working exactly the way you want them to.  But, when you master these functions you'll find them to be useful in many situations.

          1 of 1 people found this helpful
          • 2. Re: Index Table Calculations
            Joshua Milligan

            Dan,

             

            Good explanation.  I was wondering why you need the set in this example.  I get that you are using it to enforce a specific sort and also to demonstrate that the placement will affect the windowing, but even if it is removed then the data engine's default sort works fine in this example.

             

            Regards,

            Joshua

            • 3. Re: Index Table Calculations
              Jonathan Drummey

              Some extra comments on what Dan wrote:

               

              - Everything on the right-hand side of the Advanced Compute Using is addressing, everything left over is partitioning (plus discrete aggregate pills with "Ignore in Table Calculations" turned off). I think of partitioning as defining the size/number of buckets that Tableau will be doing the calculations in. Addressing and setting the Compute Using on a pill's context menu (and computing along and running along if you are reading old documentation) are synonymous, I think of addressing as defining the number of times within the bucket that Tableau will be performing the calculation. An oddity to me is that we define the addressing first, but I tend to think in terms of partitioning first. The advantage to doing it Tableau's way is that in many cases it's easy to predict what will happen because those new dimension pills in the view are automatically added to the partitioning.

               

              - The order of the dimensions on the right-hand side of the Advanced Compute Using is important for determining what's available for Restarting Every & At the Level, and can affect the outcome of results for some calcs such as INDEX().

               

              - All the dimensions on the right-hand side of the Advanced Compute Using form a "set" that is sorted by whatever the chosen Sort options are at the bottom of the dialog. Choosing a single dimension for the Compute Using via the pill's context menu or the Edit Table Calculation dialog is the same as the "Automatic" sort of however the view is laid out.

               

              - Sets are really useful for sorting by multiple dimensions to achieve a desired visual layout. However, in this case the set that Dan created is not necessary for this particular view (and could slow it down if there was high cardinality in the set) because the sort order for both the Set and the Ranking calc are the same.

               

              - Restarting every gives us a finer-grained control over the partitioning. The options in the Restarting Every are what's on the right side of the Advanced Compute Using (the addressing), with the exception of the bottom-most field because that would be restarting the partition on every row, effectively the same as Compute Using->Cell.

               

              - The default At the Level setting of Deepest is the same as whatever the bottom-most field on the right-hand side of the Advanced Compute Using, so the recommendation to set At the Level to Name is not necessary in this case. What shows up in the At the Level drop-down are all fields that are below (on the right-hand side of the
              Compute Using) whatever has been chosen for Restarting Every, and what shows up in Restarting Every is everything that is above whatever has been chosen for At the Level.

               

              - Nathan had mentioned that this was a simplified view, it's important to know that table calculation results are dependent on the shape and sparseness of the data, pill types and arrangement in the view, Mark Type, whether dates are being used (and where), etc. There's a lot that's not been documented about table calcs, I've been trying to capture the best descriptions in this post: http://drawingwithnumbers.artisart.org/want-to-learn-table-calculations/.

               

              Jonathan

              2 of 2 people found this helpful
              • 4. Re: Index Table Calculations
                Joshua Milligan

                Jonathan,

                 

                That is an outstanding explanation of table calcs! 

                 

                So as I played around to better understand your explanation, I came across something that I didn't expect and I'm curious about what's going on here:

                 

                1. I've got a row-level calculated field that simply filters rows where Category is "Furniture" and Region is "East".

                2. Index is computed at the level of Region.

                3. I've turned on "Show Empty Rows"

                 

                table calc.PNG

                 

                Now, if I put Region and Category on Rows and Columns respectively, I understand that Tableau does some data densification and I get this:

                 

                table calc 2.PNG

                 

                But what is it doing in the first example? It's not completing the domain.  So how does it know to show the row?  I thought a simple row-level calculated field, even when used as filter, was applied at the data source.  I know that filters based on aggregate calculations and table calculations will happen later.  But this makes it look like the row-level calc filter was evaluated later.  Any thoughts or explanations?

                 

                Regards,

                Joshua

                 

                 

                 

                By the way, it's the same behavior I get when using a set as a filter.  But I could better accept a set being evaluated in the data engine instead of the data source as opposed to a row-level calculation.

                setfilter.png

                • 5. Re: Index Table Calculations
                  Nathan Schofield

                  WOW! Thanks Dan and Jonathan for the in-depth explanations. I will review in more detail and post any further questions if they come up. Brilliant to be able to get advice on this from both of you. Really appreciate this.

                   

                  Nathan

                  • 6. Re: Index Table Calculations
                    Jonathan Drummey

                    Hi Joshua,

                     

                    I'd never noticed Show Empty Rows/Show Empty Columns before, thanks for pointing those out! I spent a little time testing this (ok, enough time to create 45 worksheets with notes in the attached) and have an explanation for what you're seeing, along with an interesting wrinkle or two.

                     

                    I regenerated your example of filtering out Furniture/East and I can see in the Tableau logs that the data really is filtered out from the query to the datasource whether using the row-level calculated field or a Set Filter. However, when I turn on Show Empty Rows I can see in the tabprotosrv.log the "tuple" word for those worksheets. So far in studying Tableau, "tuple" has always indicated that Tableau is doing some sort of densification with the data returned from the query. I'm pretty sure that explains the behavior.

                     

                    Note that the densification only occurs when there is a value of the dimension(s) in the data set (so long as those dimensions are not a Date or Bin, more on that in a bit). So, for example, if we completely filter out East it doesn't show back up in the padding, it's only when one or more values of East are *not* filtered out do we see Show Empty Rows working to fill in East.

                     

                    The next interesting bit is that even though the Furniture/East has been densified/padded, it is *not* addressable, so far as I can tell. By addressable I mean "some sort of access to viewing/setting the value of a cell." I tried using assorted calcs, table calcs, and using the Format->Pane->Special Values->Text field, and there's nothing there in that cell (or row) that is filled in by Show Empty Rows. That's something I hadn't seen before in Tableau, so far all the padding I've seen has been addressable.

                     

                    Dates and Bins are special in Tableau in that we can get domain padding for them by turning on Show Missing Values. It turns out that if you turn on Show Missing Rows for a Date or Bin, it will pad but not be addressable like other dimensions. However, if you turn on Show Empty Columns for a Date or Bin, it is addressable just like having turned on Show Missing Values, and you can even blend to the padded data (just like Show Missing Values). However, when we switch to continuous dates the behavior is slightly different (see below for exact details). Also, that padding plus addressing from for dates & bins from Show Empty Columns (or Rows) goes away if you move the dimension off of Columns or Rows, unlike Show Missing Values. I have no idea why Tableau does this.

                     

                    Also like Show Missing Values, the padding here is only within the available range that is returned from the data, if you completely filter out one end of the range then that won't get padded.

                     

                    So, here's a list of behaviors I've identified. Note that all of this is *only* true when domain completion and/or domain padding have not otherwise been triggered by pill arrangement, Mark Type, etc. Domain completion, domain padding, and the "Show Empty Columns/Rows" padding for dates and bins can all create similar results.

                     

                    Bins or discrete dates on Rows

                    Show Missing Rows - padded but not addressable

                    Show Missing Columns - padded *and* addressable (and blendable)

                     

                    Bins or discrete dates on Columns

                    Show Missing Rows - not padded at all

                    Show Missing Columns - padded *and* addressable (and blendable)

                     

                    continuous date on Rows

                    Show Missing Rows - not padded at all

                    Show Missing Columns - padded *and* addressable (and blendable)

                     

                    continuous date on Columns

                    Show Missing Columns - padded *and* addressable (and blendable)

                    Show Missing Rows - not padded at all

                     

                    other discretes on Rows

                    Show Missing Rows - padded but not addressable

                    Show Missing Columns - no effect

                     

                    other discretes on Columns

                    Show Missing Rows - no effect

                    Show Missing Columns - padded but not addressable

                     

                    Finally, I don't understand the documentation at http://onlinehelp.tableausoftware.com/v8.0/pro/online/en-us/missing_values.html. It's showing padding happening but given the pill arrangement in the screenshots I can't duplicate that behavior, I'll be asking support about that one.

                     

                    Jonathan

                    • 7. Re: Index Table Calculations
                      Joshua Milligan

                      Jonathan,

                       

                      Where do you get the time to do everything!?

                       

                      Thanks for the extensive testing and explanation.  That's very helpful.  A few observations:

                      #1. I can filter out East completely and still have the empty rows show - but only if I use a copy of the Region field for the filter.

                      #2. If I use a field where the values are distinct for Category, then Show Empty Rows does not appear to cause any densification. 

                      #3 I can of course get densification to occur by rearranging those fields (regardless of whether Show Empty Rows is on or off).

                       

                       

                      #1

                      Capture.PNG

                       

                      #2

                      Capture 2.PNG

                       

                      #3

                      Capture 3.PNG

                      • 8. Re: Index Table Calculations
                        manu joseph

                        What advanced option should I use if I want to use compute using cell all the time for a particular measure I have created?

                        • 9. Re: Index Table Calculations
                          JANIT KAPIL

                          This is great great explanation.

                           

                          I have a scenario where you expand the row shelf as a hierarchy and ion that it doesn't work.

                          Basically, when I drill down by any pill, the computing rank doesn't work, becomes red. Do you have any workaround?