12 Replies Latest reply on Oct 3, 2013 10:12 AM by Rossella Blatt Vital

    Table Calculation: Top N + Others + sorting

    Rossella Blatt Vital

      Hi all,

       

      I need some help on computing a table calculation in the right way.

       

      I have two dimensions (chosen by the user through a parameter): LEVEL 1 and SPLIT. For example, LEVEL 1 can be = CLIENTS and SPLIT can be = EXCHANGE.

      I would like to display a table displaying each LEVEL 1 element (e.g., each client), its top 10 SPLIT (e.g., exchanges), with top defined by the measure [T1 LOTS - GIVE IN] and all the other SPLIT elements grouped as "OTHERS".

      This is the best I was able to get:

       

       

      I defined the calculated field Index (INDEX()) that ranks each SPLIT by addressing LEVEL 1 and SPLIT and sorts by T1 LOTS - GIVE IN:

       

       

      "In top 10?" is a boolean calculated field defined as:

      [Index] <= 10

      and with the same Table Calculation configuration as Index.

       

      The calculated field [SPLIT with OTHERS grouped (CLIENT OR EXCHANGE )] is defined as:

      IF [In top 10?]
      THEN ATTR([SPLIT])
      ELSE "OTHERS"
      END

       

      The measure displayed is a calculated field [T1 WINDOW SUM LOTS - GIVE IN] defined as:

      WINDOW_SUM(SUM([T1 LOTS - GIVE IN]))

      The Table Calculation for [T1 WINDOW SUM LOTS - GIVE IN] is "cell".

       

      The last part that I can't have working with the rest are:

       

      1) "grouping" the OTHERS, i.e., all SPLIT = OTHERS should be grouped together and the measure should be summed. For example, for the client in the image below I would like to see one row for "OTHERS" and the corresponding measure = 3272 (the sum of the single values for all the OTHERS of that client). The columns that I will display will be: LEVEL 1, [SPLIT with OTHERS grouped (CLIENT OR EXCHANGE )] and [T1 WINDOW SUM LOTS - GIVE IN].

       

       

      2) Display only NOT EMPTY rows. I tried dropping the  [T1 WINDOW SUM LOTS - GIVE IN] measure to the filter shelf and select special>non-null values but the empty rows didn't disappear.

       

      3) make sure the sorting works fine. the sorting should be:

      LEVEL 1 (by [T1 WINDOW SUM LOTS - GIVE IN])

      For each LEVEL 1, the rows should be sorted by [T1 WINDOW SUM LOTS - GIVE IN] with OTHERS always at the end

       

      Many thanks in advance for any help.

      Rossella

        • 1. Re: Table Calculation: Top N + Others + sorting
          Shawn Wallwork

          Hi Rossella,

           

          For #1 move your calc to be the second from the left in on the row shelf and it will roll-up the others:

           

          Move.png

           

          For #2 what you tried should work. Try it again. And check to see what the compute using is set to. Table down should work.

           

          For #3 Will 'Others' always be the lowest value, or a NULL?

           

          --Shawn

          1 of 1 people found this helpful
          • 2. Re: Table Calculation: Top N + Others + sorting
            Rossella Blatt Vital

            Hi Shawn,

             

            thank a lot for your help.

             

            I followed your instructions for # 1 and it's correctly rolling up SPLIT (I see only one OTHERS), but it is not rolling up the measure:

             

             

            For # 2 I changed the measure and I am using directly the original measure [T1 LOTS - GIVE IN] rather than the calculation [T1 WINDOW SUM LOTS - GIVE IN] defined as:

            WINDOW_SUM(SUM([T1 LOTS - GIVE IN]))


            # 3: Others will NOT always be the lowest value, or a NULL.

             

            Thanks for your help.

            Rossella

            • 3. Re: Table Calculation: Top N + Others + sorting
              Shawn Wallwork

              For #1 that's because you have Split on the row shelf. Tableau takes a look at whatever dimensions are in the view and then sets up the rows accordingly. The dimension to the farthest left will be the top of the hierarchy, then the next pill divides up this first one into more detail, more rows. The next one does the same, moving all the way across until you get to the measure. If you want to roll-up the Others into a single value then you will need to roll up the Split field into the same aggregation as you did with the Split Others (or just take it off the row shelf). Try starting with just Level 1 and Split Others on the row shelf. This should give you what you want. Then add other pills and see what these do to the viz.

               

              Hope this helps,

               

              --Shawn

              1 of 1 people found this helpful
              • 4. Re: Table Calculation: Top N + Others + sorting
                Rossella Blatt Vital

                Hi Shawn,

                 

                thanks again for your help.

                If I remove SPLIT from the row shelf, [SPLIT with OTHERS] will become *:

                 

                 

                The field is defined as:

                 

                IF [In top 10?] // check if the record is in the top 10: if yes,use its original name, otherwise call it "OTHERS"
                    THEN ATTR([SPLIT])
                    ELSE "OTHERS"
                END

                 

                You mentioned that another option is to "roll up the Split field into the same aggregation as you did with the Split Others"L: what do you mean? How do I do that? SPLIT doesn't use any table calculation. it is defined as:

                 

                IF [Select Client, exchange or Instrument] = "CLIENT"

                THEN [ALL EXCHANGES]

                ELSE // instrument or exchange

                [ALL CLIENTS]

                END

                 

                Thanks in advance

                Rossella

                • 5. Re: Table Calculation: Top N + Others + sorting
                  Shawn Wallwork

                  Is Split coming from a secondary data source? Or does Split with Others involve a secondary data source?

                   

                  --Shawn

                  • 6. Re: Table Calculation: Top N + Others + sorting
                    Rossella Blatt Vital

                    I have one data source which is a UNION all of two tables (actually more, but this view regards only two of them). The first table contains the measures for the previous business day. The second table contains the same measures for 6 months of data aggregated at a weekly level.

                    In this specific dashboard I need to display the top clients (or exchange or instrument depending on the selection done by the user through the parameter) for the previous day. This is the table displayed in my previous posts. Then I need to display the trend of those top clients (top for the previous day, but not necessarily for the year).

                    I created a series of calculated field  that are basically the union of the corresponding field, for example:

                    FIELD: [ALL CLIENTS]

                     

                    IF ISNULL([WL_CLIENT_NAME])

                    THEN [T1L_CLIENT_NAME]

                    ELSEIF ISNULL([T1L_CLIENT_NAME])

                    THEN [WL_CLIENT_NAME]

                    END

                     

                    The prefix WL corresponds to the weekly data, whereas T1L corresponds to the previous day data.

                    I did this for all dimensions, but I didn't change the measures.

                    So for example, if I drop ALL CLIENTS and T1_LOTS to the rows and columns shelf I will see only the previous day clients (because all other clients will have Null as T1_LOTS). In this way, if the user selects a client and there is a second view displaying the weekly information, the client will be passed correctly.

                     

                    I had to use this approach because none of the others that I tried worked for all the "linking" that I needed to do.

                     

                    I am sorry if I didn't mention all this before: I though it wasn't relevant to the specific problem and I didn't want to provide more info than necessary.

                     

                    I'd rather not change this configuration if possible. Do you think there is a way to have the problem sorted out even if I am using these calculated fields?

                     

                    Thanks

                    Rossella

                    • 7. Re: Table Calculation: Top N + Others + sorting
                      Rossella Blatt Vital

                      Hi,

                       

                      I tried using only the fields from T1L, but I still have the same problem:

                       

                       

                      I Have substituted LEVEL 1 with T1L_CLIENT_NAME (this field is actually a parameter but for this example I am using one of the possible values of the parameter). T1 SPLIT with OTHERS is defined as:

                       

                       

                      and [In Top 10} as:

                       

                       

                      with [Index] = INDEX();

                       

                      But it looks like the same problem of multiple attributes (*) occurs also in this case.

                       

                      So maybe the problem has nothing to do with the datasource. Perhaps I am doing something else wrong..

                      What do you think? Any idea?

                       

                      Thanks

                      Rossella

                      • 8. Re: Table Calculation: Top N + Others + sorting
                        Rossella Blatt Vital

                        Also, this is how SPLIT is defined:

                         

                        IF [Select Client, exchange or Instrument] = "CLIENT" // this is the parameter

                        THEN [ALL EXCHANGES] // [ALL EXCHANGES] is the union of T1L_EXCHANGES and WL_EXCHANGES (previous day and weekly data)

                        ELSE // instrument or exchange

                        [ALL CLIENTS] // [ALL CLIENTS] is the union of T1L_CLIENTS and WL_CLIENTS (previous day and weekly data)

                        END

                        • 9. Re: Table Calculation: Top N + Others + sorting
                          Rossella Blatt Vital

                          The only way I could get this work was by using a set to define the top 10 SPLIT as explained here:

                           

                          http://kb.tableausoftware.com/articles/knowledgebase/top-n-and-others-sets

                           

                          The problem with this is that it will select the top 10 SPLIT overall and not for the specific partition (each LEVEL 1 will have a different list of TOP 10 SPLIT).

                           

                          Any idea how to get this working for more than one partition?

                          Thanks

                          Rossella

                          • 10. Re: Table Calculation: Top N + Others + sorting
                            Shawn Wallwork

                            Rossella,

                             

                            Unfortunately there are just too many moving parts for us to help you without being able to get our hands on your workbook. Unless you can sanitize the data and post a packaged workbook, I really don't think there's much I can do to help you. Sorry,

                             

                            --Shawn

                            • 11. Re: Table Calculation: Top N + Others + sorting
                              Rossella Blatt Vital

                              Hi Shwan,

                               

                              thanks. I will try to create a sample workbook that replicates the problem.

                               

                              Thanks anyway for your help so far.

                              Rossella

                              • 12. Re: Re: Table Calculation: Top N + Others + sorting
                                Rossella Blatt Vital

                                Hi Shawn,

                                 

                                please take a look at sheet 2 in the workbook in attachment (modified from: http://community.tableau.com/thread/109093)

                                 

                                I just added an additional dimension on the rows shelf. Basically I am trying to find the top N Customer Names per Customer Segment. All customers not falling in the top N (in my case actually not a parameter but a static number)  will fall into "Others". The measure displayed for others should be the SUM of the measures of each single customer classified as Others for that category.

                                 

                                Finally, the customers in each Customer Segment pane should be sorted by the displayed measure (descending) and the last row for each  Customer Segment pane should be "Others" and the corresponding measure.

                                 

                                I am almost there but there is still something odd going on. For example, when setting the TOP parameter = 182, the customer name with index 182 is not in the top N group (see image below) and the sum of the others is wrong:  it should be 973..

                                 

                                 

                                Thanks a lot for any help.

                                Rossella