6 Replies Latest reply on Sep 22, 2016 11:10 PM by Sreekanth Kasaraneni

    Allow user to select Top 5 or Bottom 5 Parameter

    Cheri Beckman

      I have a map with customer locations by Zip Code.  User wants to view Top 5 Zip Codes by #Customers, OR Bottom 5 Zip Codes by #Customers, on same map. 

       

      I have set up a Parameter that allows selection of All or Top 5 which works fine.  How do I give them the choice to select top or bottom?  Thought about setting a parameter with Top 5 and Bottom 5 and All as choices, but don't know how to filter on either/or.  Thought about calling a different set (Top 5 Set or Bottom 5 Set) based on a parameter selection (Top or Bottom) but that didn't work either.

      Thanks. 

        • 1. Re: Allow user to select Top 5 or Bottom 5 Parameter
          Marshall Mills

          Hey Cheri,

           

          Since the list of choices are so small; I'd recommend converting the parameter to "Multiple Values (list)".

           

          Also, as you had mentioned, if you can change the selection to

          • Top 5
          • Bottom 5

           

          This will give the end-users 3 choices:

          Top 5Bottom 5Results
          No filtering.  Show everything.
          Only show bottom 5.
          Only show top 5.
          Show top 5 and bottom 5; but nothing else.

           

          Note: the parameter should only represent these 2 choices.  To be clear, don't add a "All" value.

           

           

          Hoping this answered your question,

          Marshall

          • 2. Re: Allow user to select Top 5 or Bottom 5 Parameter
            Cheri Beckman

            Thanks for your response, but how do I assign what to show for each selection?  Filter formula?

            I currently have a Set for Top5 and Bottom5.  How do I connect with parameter selection?

             

            Thanks,

            Cheri

             

            Cheri Beckman | Operations Analyst

             

            P: 678.836.1101 | F: 678.836.1049

            4550 North Point Parkway, Suite 180 | Alpharetta, GA 30022

            cbeckman@GoodCents.com<mailto:cbeckman@GoodCents.com>  |  www.GoodCents.com<http://www.goodcents.com/>

            • 4. Re: Allow user to select Top 5 or Bottom 5 Parameter
              Cheri Beckman

              This is very interesting and is close to getting me what I want (the option that uses Parameters).  However, I want the user to be able to select

               

              ·         All

               

              ·         Top 5 & Bottom 5

               

              I’m having difficulty tying the Parameter (user selection) to show the appropriate Set.  You can’t use Sets in IF statements, so what is the filter to accomplish this?

               

              Thanks,

              Cheri

               

              Cheri Beckman | Operations Analyst

               

              P: 678.836.1101 | F: 678.836.1049

              4550 North Point Parkway, Suite 180 | Alpharetta, GA 30022

              cbeckman@GoodCents.com<mailto:cbeckman@GoodCents.com>  |  www.GoodCents.com<http://www.goodcents.com/>

              • 5. Re: Allow user to select Top 5 or Bottom 5 Parameter
                Marshall Mills

                Cheri,

                 

                The following is rough, but I think it's close.

                 

                1. Create a new workbook
                2. Connect to an Excel file
                  File name:  "%USERPROFILE%\Documents\My Tableau Repository\Datasources\10.0\en_US-US\Sample - Superstore.xls"
                  Open > Open with Legacy Connection
                3. From Data Source, double-click "Orders"
                4. Create a "New Custom SQL"
                  SELECT [Postal Code],
                                 Count( [Order ID] ) AS orderIdCount
                  FROM [Orders$]
                  GROUP By [Postal Code]
                5. Left Join "Postal Code" = "Postal Code"
                6. Rename the custom SQL to "orderIdCountByZipcode"
                7. Create another custom SQL
                  SELECT topCount.*,
                         'top 5' AS placement
                  FROM ( SELECT TOP 5 topOrderIdCount.orderIdCount
                         FROM ( SELECT DISTINCT Count( [Order ID] ) AS orderIdCount
                                FROM [Orders$]
                                GROUP By [Postal Code]
                              ) topOrderIdCount
                         ORDER BY topOrderIdCount.orderIdCount DESC
                       ) topCount

                  UNION

                  SELECT bottomCount.*,
                         'bottom 5' AS placement
                  FROM ( SELECT TOP 5 bottomOrderIdCount.orderIdCount
                         FROM ( SELECT DISTINCT Count( [Order ID] ) AS orderIdCount
                                FROM [Orders$]
                                GROUP By [Postal Code]
                              ) bottomOrderIdCount
                         ORDER BY bottomOrderIdCount.orderIdCount ASC
                       ) bottomCount
                8. Left Join orderIdCountByZipcode.orderIdCount = orderIdCount
                9. Rename the custom SQL to "topAndBottomOrderCounts"
                10. Go to Sheet 1
                11. Drag Postal Code to Columns
                12. Drag orderIdCount from orderIdCountByZipcode to Rows
                13. Right mouse-click SUM( orderIdCount) and change it to Attribute
                14. Sort Postal Code descending by orderIdCount
                15. Select placement under Dimensions and "Show Filter"

                 

                The only thing I couldn't figure out is how to get the NULL entry for the placement filter to say "middle" instead of Null.

                 

                Notes

                • The "Open with Legacy Connection" is required in order to be able to create custom SQL.
                • The DISTINCT from that ugly ugly UNION is needed so you get the top and bottom 5 count values.
                  Take off the DISTINCT from the bottomOrderIdCount; You'll see what I mean.

                Someone please!  There has got to be an easier way to do what Cheri is asking.  That UNION statement is just hack-o-rama.  But, it works.

                 

                 

                Hoping this answered your question,

                Marshall

                • 6. Re: Allow user to select Top 5 or Bottom 5 Parameter
                  Sreekanth Kasaraneni

                  Hi Cheri,

                   

                  Please check the attached workbook which might help you

                   

                  1. Create a Top N Set
                  2. Create a Bottom N Set
                  3. Combine the two sets into a combined set and drop that in your filter shelf
                  4. Place either top or bottom set onto colour

                   

                  Thanks,

                  Sreekanth