4 Replies Latest reply on Jul 16, 2012 10:11 AM by clement.luton

    how to sort a table on sub totals?


      I want to calculate the nb of account accessed by device and sorted first by device with the highest nb of accounts.


      So I have one table with 3 columns:

      - Device

      - Account

      - Nb of distinct account (so always 1)

      and I have a sub total for each device.


      How I could sort on a sub total?

        • 1. Re: how to sort a table on sub totals?
          Jonathan Drummey

          Attached is one method. A table calculation called WD CountD nb_merchant with the formula -WINDOW_SUM(COUNTD(nb_merchant)) is made Discrete and added to the Rows shelf, with Compute Using set to account_name (so it recalculates for each device_uuid. Then uncheck Show Header and the view is sorted, as in the attached.


          One issue with this method is that since a table calculation is being used to lay out the view, Tableau is not able to properly compute totals or subtotals. If you still need those, some other method would be needed.



          • 2. Re: how to sort a table on sub totals?

            Hi Jonathan,


            That's sweet of you, nice trick and thanks for your help.


            I have another problem: I want to add additional information for each account for example one email. So one more column,

            But if I do that then the table is not anymore sorted because the window is based on the pane and take into account the device uuid and the accountname. Do you have some hints?



            • 3. Re: how to sort a table on sub totals?
              Jonathan Drummey

              When you bring additional non-aggregated dimensions into the view (like email), Tableau does not change the Compute Using (which is also called addressing at times), so the new dimensions are added to the partitioning. Since the email address is more granular than account_name, it's increasing the level of detail of the view and changing the results of the table calculation.


              In this case, you need an Advanced... Compute Using to include both the account_name and email, so the partitioning will remain on device_uuid. You get there by right-clicking on the blue WS CountD nb_merchant pill on the Rows Shelf and choosing Edit Table Calculation..., then choosing Advanced... from the Compute Using drop-down, then setting it as follows:


              Screen Shot 2012-07-13 at 4.46.55 AM.PNG


              I've also attached a workbook with a new view as well.



              • 4. Re: how to sort a table on sub totals?

                Hi Jonathan,


                That's awesome! It works fine...


                Thanks for your help!