8 Replies Latest reply on Oct 15, 2012 6:35 AM by John Munoz

    How to sort beyond the range of a filter?

    John Munoz

      Hi Super Tableau Users,

       

      I've attached the Superstore sales workbook to this question in an attempt to show what I'm running up against.

       

      Simply put, I want to be able to select the top N records in a given quarter, based on a sort of quarter over quarter change in sales. I think I'm almost there, but here's the problem. If I apply the filter to say the first 10 records, and then sort a given QoQ column, Tableau only sorts w/in the first 10 records. I'm trying do achieve this with the index function (ranking the QoQ values), and I think the index doesn't 'see' what's beyond the filter.

       

      The attached workbook has a slider for the top N values. To see what I'm talking about, move the slider to 10, then sort a QoQ change column. After you do the sort, move the slider all the way to 17, and you'll see the limitation.

       

      I tried to work around this with a parameter control, but no luck. I need something that'll let me sort all of the data, beyond the filter, and then apply the filtering criteria.

       

      Earlier, I tried making a set (I think I read that in Tableau help), but the real data I'm using contains over a million rows and is highly cardinal (I think that's a fancy way of saying, lots of unique values). The one set I tried was way too slow.

       

      Thanks so much!

        • 1. Re: How to sort beyond the range of a filter?
          Alex Kerin

          The index is wrong - we need to explicitly set it to QoQ, but can't because QoQ is a table calc. Still thinking....

          • 2. Re: How to sort beyond the range of a filter?
            John Munoz

            I feel smarter already knowing that this problem will take a pro like you more than 2 minutes to figure out.

             

            Thanks Alex!

            • 3. Re: How to sort beyond the range of a filter?
              Jonathan Drummey

              The one-click sort is a visual sort, so Tableau doesn't do a lot of recalculating. However, when you use a parameter, Tableau does more recalculating. I set up a parameter, then changed the index filter to INDEX()<=[Top N Param], and as far as I can see it works as desired now.

               

              Jonathan

               

              PS: The one downfall of this solution is that you can't dynamically set the parameter range, so you have to choose a number big enough for the data set.

              • 4. Re: How to sort beyond the range of a filter?
                John Munoz

                Thanks Jonathan, but that didn't do it.

                 

                For example, when I open your workbook and sort by QoQ change in 2011 Q3, all is good, because the parameter control is wider than the full set of 17 sub cats. However, let's say I move the slider to top 13, and then sort QoQ change in 2012 Q4, Do that, then open up the slider to 17 or more and you'll see that the data below row 13 are not sorted.

                 

                We need a way to trick Tableau into doing work above and beyond the visual layer.

                 

                I very much appreciate your time and effort here.

                • 5. Re: How to sort beyond the range of a filter?
                  Jonathan Drummey

                  Sorry about that, my brain wasn't working right on a Saturday morning. I was looking at Sales and not the QoQ change. The Sales will retain order when you sort, but the QoQ change does not…interesting inconsistency there, I'm imagining this is because the one-click sorts are still a fairly new feature and sorting by the results of table calcs is tricky.

                   

                  I'll play around some more with this, right now I'm framing this as a situation where we need to sort by the result of a table calc (the QoQ change), then filter those sorted results (the Index filter). Alex, if you have any ideas, I'd love to see them!

                   

                  Jonathan

                  • 6. Re: How to sort beyond the range of a filter?
                    Alex Kerin

                    I keep coming up blank - I would love to see a feature where we could create a table view, and then use this as another source. If the data changes, the new 'source' changes...

                    • 7. Re: How to sort beyond the range of a filter?
                      Jonathan Drummey

                      Alex - that would be lovely!

                       

                      John - I don't believe the quick sort will work in this case, there's no way I can see to make the sort extend through the data hidden by the Index filter when the quick sort is on a table calculation. I tried using a parameter to choose the Quarter, and a discrete calculated field to generate a QoQ number that can be used to force Tableau to sort the results. That works fine for sorting the results, however, the Index filter also needs to use that same sort and there's no easy way to do that because we'd need to address and partition on the results of a table calculation. I've been playing around with techniques for doing just that, but I'm not at a point yet where I can come up with a solution using that method.

                       

                      I do have a theory about a workable alternative, though, and that's to use Custom SQL to pre-join the prior year's results so the calcs can stay aggregate calcs and not get into table calc craziness. If you need help, let me know and I'll see what I can do in the next day or so - I don't have time to put it together just now. Here's my idea, based not the Superstore Sales view you'd set up:

                       

                      - Create an aggregate query that groups the Superstore Sales Orders table on quarter of Order Date (plus 1 year) and Product Sub-Category, also returning SUM([Sales]). This gets us the oQ of the QoQ, so let's call the measure oQ Sales.

                      - Use the original Superstore Sales query as the left side of a left join, and the aggregate query as the right side of the join. The join fields in Superstore Sales are quarter of Order Date and Product Sub-Category, to quarter of Order Date (plus 1) and Product Sub-Category from the aggregate query. We're using a left-join because some of the results of the join will be Null.

                      - In Tableau, create a QoQ Sales calc that is something like SUM([Sales])-ZN(ATTR([oQ Sales]))/ZN(ATTR([oQ Sales])). I'm using ATTR as a sanity check, since the oQ Sales is a pre-aggregated measure, I want to make sure that if I'm choosing the a finer or coarser level of detail in the view (like by having Product Name in the view, or just year of Order Date) that the QoQ Sales calc returns no results rather than incorrect results.

                      - Sort the Sub-Category by the QoQ Sales calc, Descending.

                       

                      At this point, you shouldn't have to do anything with the Index field, there should be a one-click quick sort option available for QoQ sales, and the sort should extend through the hidden data just as it does for the Sum([Sales]) measure. Also, using this option you could use a regular filter on the quarter of Order Date and not a table calc filter, which would improve performance.

                       

                      Jonathan

                      1 of 1 people found this helpful
                      • 8. Re: How to sort beyond the range of a filter?
                        John Munoz

                        Hi Jonathan,

                         

                        I got some one-on-one time with Joe Mako yesterday and he walked me through a solution quite similar to yours. Your zn part of the calc for delta is a great idea.

                         

                        I will post it sometime this week.

                         

                        Thanks for much for your thoughtful input.