7 Replies Latest reply on Nov 11, 2010 2:12 AM by Richard Leeke

    How to specify ordering within each partition independently

    Richard Leeke

      I've just realised that the "Order Along" setting in the Advanced Table Calc dialog is ordering by the aggregate measure across all partitioning fields.  I had been assuming it was sorting the rows within each partition independently.  That will teach me to check my results better.

       

      Is there any way to do what I'm trying to do?  I had a bit of a go with sets to see if I could use the approach you use for nested sorting on a worksheet - but couldn't see any way.

        • 1. Re: How to specify ordering within each partition independently
          Richard Leeke

          I seem to have got this doing what I want by duplicating the partitioning fields, adding the duplicate fields to LoD and then specifying those as addressing fields.  Does anyone know if this is the best approach?

          • 2. Re: How to specify ordering within each partition independently
            Joe Mako

            This is a similar situation as nested sorting, see http://kb.tableausoftware.com/articles/knowledgebase/nestedsorting

             

            To deal with this in table calculations, you will need to make a calculated field instead of a set, like:

             

             

            [Product]+[State]

             

             

             

             

            And use that for your partitioning/addressing instead of the individual fields. I can't say exactly how you need the Edit Table Calculation dialogs setup, because every situation is different, but you will likely need to use advanced, and change "At the level" to this calc field.

             

            Then if you still want to display Product and State nicely in a tool-tip, you can put them on the Level of Detail shelf and change them from a dimension to an attribute, wrapped in ATTR(). ATTR() will default to not be included in a table calculation.

            • 3. Re: How to specify ordering within each partition independently
              Richard Leeke

              Thanks Joe, that does the trick in the case I'm looking at.

               

              I can still foresee that there could be a problem if I were to need to reference (in this example) [Product] & [State] individually as well as the combined field within the table calculation, though.

              • 4. Re: How to specify ordering within each partition independently
                Joe Mako

                The ability to reference the individual fields is solved with the ATTR() aggregation, and not including them in the table calculation (an option from the context menu for the pills that is enabled by default for ATTR aggregation).

                • 5. Re: How to specify ordering within each partition independently
                  Ross Bunker

                  Richard,

                  Yes, the sorting is done on the given measure for just the ordering fields specified.  Sorting is not done separately for each partition, it is the same for all partitions.  The behavior is intended to mimic the behavior of defining a set and using the set for ordering.

                  If you want to get the behavior of 'resorting' for each partition, you can add all the partitioning fields to the beginning of the ordering list (using advanced) then move 'restarting every' down to the last field you want to be partitioning.  This will define the sort across all fields, but then limit the actual ordering fields to those after 'restarting every' in the list.

                  We're thinking about ways to make this case simpler.

                   

                  :)ross

                  • 6. Re: How to specify ordering within each partition independently
                    Richard Leeke

                    Thanks Ross.

                     

                    I think I tried the route of using 'restarting every' but ran into another roadblock before trying the two approaches described above (my solution of duplicating fields and Joe's solution of concatenating fields).

                     

                    I'm putting together a sample to go with the percentile calculation I keep promising to post for James's TCRL - and I keep finding extra twists that make it harder and harder.  I think I'm there now - I just need to make sure it's still giving the correct answer before I write it up and post it.

                     

                    I'll see if I can work out what the problem was with just using 'restarting every' and post that sample on this thread as a real example for you of some of the challenges.

                     

                    One of the issues (which Joe's concatenate approach gets round) is that with a mark type of line I wasn't getting lines on the viz (even with the help of the Path shelf) because of having multiple fields on LoD.

                    • 7. Re: How to specify ordering within each partition independently
                      Richard Leeke

                      Here's that sample workbook showing some of the challenges I hit with multiple ordering fields.  I've annotated the sheets charting my progress.

                       

                      Some of the issues turned out to be because I was trying to suppress the display of duplicate marks, since I'll be using this sort of approach on hundreds of thousands or millions of rows.  As I'm displaying a percentile calculated over a partition - the value will be the same for each row in that partition, so displaying hundreds or thousands of copies of each mark seems pointless, and I'm sure will carry a performance penalty.  But just not worrying about the duplicates turned out to get round some of the issues.  That may just be because of the number of fields on LoD in this case, so I suspect that some of the issues may come back with more fields in play, however.

                       

                      Anyway, I now have multiple ways of getting this workbook going and have run out of excuses for not writing up that quantile calculation I've promised for the TCRL.  (Except I'm away for the weekend now - but the calculation is in the attached workbook if anyone's interested...)