11 Replies Latest reply on Dec 4, 2012 11:01 AM by Joe Mako

    subtotal for nested sort

    Allison Suarez

      When I have a workbook that contains a nested sort, the subtotals won't work.  The "Total" line will show up but there are no values.  I want to be able to add a subtotal to SCMAI2 or SCMAI3 or SCMAI4.  Any thoughts?

      Thanks,

      Allie

        • 1. Re: subtotal for nested sort
          Joe Mako

          What is the pill that you have on the Text shelf? Is it a calculated field or aggregated? if so, what is its formula and aggregation?

          • 2. Re: subtotal for nested sort
            Allison Suarez

            It is the "number of records" so just a count.

            • 3. Re: subtotal for nested sort
              Joe Mako

              I have not seen a situation like that. Can you please provide a sample packaged workbook that recreates your situation? does not need to be real data.

              • 4. Re: subtotal for nested sort
                Gregory W

                Hi Joe, I'm also having the same problem. See sample workbook attached where "Device" totals are blank. Note that the headers of the sets have been hidden.

                • 5. Re: subtotal for nested sort
                  Joe Mako

                  Gregory,

                   

                   

                  You provided a non--packaged workbook, so I am not able to see your situation.

                   

                   

                  Please perform an extract, and then package the workbook, provideing a .twbx with data included.

                   

                   

                  If your data is sensitive, please use fake data in the same structure.

                  • 6. Re: subtotal for nested sort
                    Gregory W

                    Hey Joe,

                     

                    Please see the packaged workbook now attached. Dummy data.

                     

                    Thanks

                     

                    Greg

                    • 7. Re: subtotal for nested sort
                      Joe Mako

                      Thanks! This is a new situation I have not seen before

                       

                      The pattern that I see is that you have have a Subtotal turned on for the pill "device" and then to the right of this pill, deeper in the Row hierarchy, you have a Set, and apparently Tableau cannot evaluate a subtotal in this situation.

                       

                      What you are asking Tableau to do based on your pill arrangement is to perform a TOTAL(SUM(1)) partitioned on platform Set 2 and device, and address on Set 1 and browser, which is effectively asking Tableau to partition on device and platform, and address on device platform and browser.

                       

                      In Tableau approach to table calculation compute using, this does not fit the construct, this is asking Tableau to treat a dimension as both partitioning and addressing, because a set in situation is like a shortcut to multiple discrete dimension pills, so these instructions are in conflict, like a logical fallacy.

                       

                      In order to approach this in a way Tableau can evaluate your desired computation, you can create another dimension, a calculated field like:

                      [device]+", "+[browser]+", "+[platform]

                       

                      and use that in place of your Set 1 pill, and you will get the result you are looking for, retaining your nested sort, without a Set and a working subtotal.

                      • 8. Re: subtotal for nested sort
                        Gregory W

                        Hi Joe, great answer, works like a charm. To follow on, if one were to recreate the table from scratch, have you any idea if there any other functionality that might provide a more elegant approach to what I'm trying to accomplish (sorted nested columns with sub-sorting)? I'm sure you're aware it's something that is trivial to do in an Excel pivot table, but I'm trying to move away from Excel because the inherent weaknesses in calculated columns...

                         

                        Thanks for your input

                        • 9. Re: subtotal for nested sort
                          Joe Mako

                          Using calculated fields that concatenate strings like the one above, materialized within an optimized extract seems like a good route to get the sorting you are looking for, but there are many factors and many available options, and there is not enough information on your situation here to make a good recommendation.

                          • 10. Re: subtotal for nested sort
                            Michael Mixon

                            Hi Joe,

                             

                            I often use concatenated string dims instead of sets because of this and formatting control (i.e. I can add spacing etc. to the concatenated field for easier reading, etc.).  In your experience, are there any issues with using a concatenated dim over a set, outside of the minimal time it takes to write the calculation?  In particular, are sets, being a native feature, more efficient in terms of performance?  I've not noticed any real differences on my data sets, but they tend never to be above a few hundred thousand rows.

                             

                            -Mike

                            • 11. Re: subtotal for nested sort
                              Joe Mako

                              This is one of those, "it depends" situations, as far as I understand. The best advice I have is to try different routes and see what works best for the exact situation.

                               

                              I like calc fields that concatenate strings, and then an extract optimized, that way they are a part of the columnar data store. I do not know if Sets are materialized in the same way.