7 Replies Latest reply on Jul 8, 2012 6:04 AM by Jonathan Drummey

    Multiple dynamic sorting, indexing and lookup

    Rama Kunaparaju

      Hi,

       

         Please find the attached workbook and Excel sheet which shows the desired output. I am trying to pick up a customer segment and see how that segment ranks with respect to order quantity, sales etc. I should be able to change the segment through a filter and all the metrics should be sorted in the descending order give me a rank and the corresponding metric.

       

        How can we do this. I could do a dynamic sort, but not multiple of them.

       

      Thank you

       

        • 1. Re: Multiple dynamic sorting, indexing and lookup
          Jonathan Drummey

          Hi,

           

          The Excel workbook you attached was empty (at least to Excel 2007), so I'm just replying from the text you wrote above and the Tableau workbook. If you set the Rank calc to compute using Table (Down), then the Rank result will change based on using the quick sort functionality that you get by clicking on the header for each measure.

           

          I'm not sure what you man by needing a multiple sort, given that there's only the one Customer Segment dimension. Are you looking to enable the user to choose a measure and then sort by that? If so, you can set the Customer Segment field to Sort on the test calculated field (Descending) and the ranks will be sorted based on the test: parameter value. However, as soon as the user uses the quick sort (which cannot be disabled, as far as I know) then that the test: parameter stops working and the only way to get it working again is to refresh the workbook.

           

          Does that help, or are you needing something different?

           

          Jonathan

          • 2. Re: Multiple dynamic sorting, indexing and lookup
            Rama Kunaparaju

            Hi Jonathan,

             

            Thanks for responding and please find a screenshot attached of what i am looking for. Basically, the user should be able to select only one category at any given time and should see all the measures and their corresponding ranks with respect to other categories.

             

            Thanks

            Rama

            • 3. Re: Multiple dynamic sorting, indexing and lookup
              Jonathan Drummey

              Hello Rama,

               

              Attached is one way to go about this: Each metric that requires a different rank has a calculated field for the rank. This is necessary because the data needs to be sorted in a different way for each calculation. The Compute using for each calc uses the Advanced... setting with Compute using on Customer Segment and the sort on the appropriate metric.

               

              In order to do the proper filtering of the data, I created a parameter based on the Customer Segment field and an additional table calculation-based filter. Filters on table calculations are applied after most all other filters, so the ranking calculations aren't affected.

               

              Finally, to generate the desired output, I used a dashboard. Tableau does not allow multiple instances of Measure Names/Values in a single view, which could make life easier in this case. So what I did was create two views, one for the ranks and the other for the quantities, then aligned them in the dashboard.

               

              Let me know if this works for you,

               

              Jonathan

              • 4. Re: Multiple dynamic sorting, indexing and lookup
                Rama Kunaparaju

                This is awesome! Thank you so much.

                • 6. Re: Multiple dynamic sorting, indexing and lookup
                  Rama Kunaparaju

                  Hi Jonathan,

                   

                    I have seen your workbook about formatting and i am trying to implement that here. Now that we have the metrics for each customer segment, i have created a new sheet with averages for those metrics across all customer segments. Now, we should compare each metric against the average and add a symbol and color (like an exclamation and red color) for the cell.

                   

                  For example if sum(order quantity) > window_avg(sum(order quantity) then "good" else "bad" end and so on for all the metrics.. Would that be possible across different sheets. In the workbook attached the cells "Order quantity", "Sales" and "Profit" are all less than the Avg quantities for the "Consumer" segment. so they should all be red and/or with an exclamation mark.

                   

                  Thank you.

                  • 7. Re: Multiple dynamic sorting, indexing and lookup
                    Jonathan Drummey
                    The original view was created using Measure Names/Values. Tableau only has one Marks card that applies to all measures used in Measure Names/Values, so there is only one Text/Color/Size/etc. Shelf, and since Tableau does not give access to the individual measures within those it's not possible to make vary the desired KPI by the specific measure's value.
                    So, what I did was duplicate the data (once for each desired measure) using Custom SQL. This can result in a ton of rows, so you may need to work with the SQL in your output, for example by doing the aggregation in the SQL. This results in one data set per measure, and the new "Measure Type" dimension. A calculated field called "Value" returns the desired measure for each measure type.
                    Now we can set up a view similar to the original worksheet that used different calculations for each, only we get the advantage of being able to do both the Rank and Quantity calcs in the same view, with one calculation for the quantity (just SUM([Value]) and a second one for the Rank. However, when doing things this way we need to be careful of how the rank is computed, so it's necessary to add the Set of Measure Type and Customer Segment to the view as well so the sorting will work properly. (That last bit of magic thanks to Joe Mako).
                    Finally, the KPI can be calculated using a simple IF/THEN statement: IF SUM([Value]) >= [Value Avg] THEN "Good" ELSE "Bad" END, where Value Avg is just WINDOW_AVG(SUM([Value])).
                    Besides increasing # of rows in Tableau, the other disadvantage to this method is that it's not as easy to have different number formats for the different measures, i.e. show $ for profit, etc. If you really want that then you'd have to create one or more calculated fields to return the desired formats and use that on the Label shelf instead of the Value measure.
                    Cheers,
                    Jonathan