7 Replies Latest reply on Apr 9, 2017 9:22 PM by Mikey Michaels

    Dynamically sort set

    Mikey Michaels

      Hello All,

      I hope this message finds you well.

      On the attached 10.2 workbook, I'm trying to sort each date independently (1/7/2015, 1/10/2015, etc.). I know I can simply click at the top and select a manual sort; however, I would like this sort to be dynamic based off the "Top N Sub-Category" parameter.

      I would like this sort to not be just based off what is in the current view, but let's say a customer was within the Top N on 1/7 then they would show up on the view; however, if they were not within the top N on 1/10, they would fall off when the 1/10 date was selected for the sort, and a new customer would be included.

       

      I hope I'm being clear on my request.

       

      Cheers,

      Mikey

        • 1. Re: Dynamically sort set
          Joe Oppelt

          When I opened this in 10.2 it said it was upgrading from 9.3

           

          What version are you actually working in?  If I make changes, I want you to be able to see them.

          • 2. Re: Dynamically sort set
            Joe Oppelt

            And while I'm looking at this, if you want someone to drop out and another to replace him on 1/10, what do you expect to see in the list of customers in the in/out set?  (We may not be going with sets depending on what you want to do here.)

            • 3. Re: Dynamically sort set
              Mikey Michaels

              Hi Joe,

              Thank you so much for your quick reply.

              I originally created the view in 8.3; however, I tried to resave in 10.1 as I'm sure people are no longer using 8.3

              I have attached a new file.

              Let me clarify my request, what I'm after in my viz is to be able to answer "who are my top X customers, based off of seats UM, for a given date". I then also want the cumulative total of the customers who are outside the top X- this would be the "Others" row of the viz. I thought a set would be the best way to do this; however, I'm open to all suggestions.

               

              Thanks!

              Mikey

              • 4. Re: Dynamically sort set
                Joe Oppelt

                10.1 attached.  (I could have done 8.3 if you needed.  As it turns out, this didn't require anything that's not available in 8.3, so what I did here will can be done in 8.3.  If this had required LODs for example, you would have had to do table calcs in 8.3.)

                 

                So what I did was create a calc that grabs the [Seats] data for the date selected in the parameter.  Then the Set is based on that.  Play with it and you'll see what I mean.  (I expanded the Top-N parameter to a bigger number so I could see more data and play with it to make sure my suggestion worked.  But you can see it with a small set.  Pick 2 for Top N, and select Jan 12.

                • 5. Re: Dynamically sort set
                  Mikey Michaels

                  Hi Joe,

                  THANK YOU SO MUCH!

                  I have one follow-up inquiry: the sort works correctly when selecting 1/7/2015, but not when selecting 1/11/2015. See screen shot below.

                  Any idea as to why this is occurring?

                   

                  • 6. Re: Dynamically sort set
                    Joe Oppelt

                    Ah.  I forgot about ranking.

                     

                    OK, so here's what I did.  I created a RANK calc.  Sheet 5 is a copy of Sheet 4, but I added the calc.  When I did, I forgot about how Tableau puts NULL at the top of ranking.  (When you put a table calc as a discrete pill on ROWS or COLUMNS, tableau will sort by its value at the level where you place it.  I placed it before customer name, so it is sorting the customer names by the rank value.  And I am ranking the special calc that grabs only the seats for the selected date.)

                     

                    Tableau is obedient, and it is giving rank values of NULL to the marks that are not in the selected date.

                     

                    So here's what I did.  See Sheet 6.  I did another table calc that will force the RANK value to all the dates across the row.  (See Calculation 2).  Do "Edit Table Calc" on the Calculation2 pill on Rows.  You will notice that in the edit-calc dialogue box there is a pull-down for "Nested Calculations".  RANK is nested in Calculation2.  I told RANK to do TABLE(down) (like it was on sheet 5), but I want the rank number to be propagated across all the dates in the row so that the NULL gets overridden.

                     

                    I hope I explained that OK.

                     

                    So now you have that Calculation2 displayed on your sheet.  Right click on the Calculation2 pill, and uncheck "Show Header".  Now it's on the sheet doing its job, but it's not displayed.

                    • 7. Re: Dynamically sort set
                      Mikey Michaels

                      thank you so much, Joe