1 Reply Latest reply on Nov 14, 2013 2:16 AM by Jim Wahl

    Trying to sort 2 dimensions dynamically

    Kristin Chula

      I was wondering if there was a way to create a data table that can rank values assigned by 2 dimensions more dynamically.

       

      I have attached a workbook (8.0) that illustrates the structure and type of data I am working with.

      I start with just 2 dimensions: Customers and Reasons.  Customer 1's largest (as ranked by measure) two reasons are (i ) and (a), but Customer 2's largest two reasons are (j) and (c):

       

      row shelfcolumnshelf
      ReasonCustomer1Customer2
      a28459220833
      c26185265619
      i4605576609
      j67623184051

       

       

      What I'm hoping to come up with is a data table that shows:

      Header 1Header 2Header 3
      Customer 1Customer 2
      Top Reasonij
      2nd Top Reasonac

       

      I actually did find a way to display the 1st largest reason for each Customer in a data table, but I'm stuck on how I could also show the 2nd largest reason as well.

      Is there a way to do this - not necessarily using my method, but another way?

       

      Thanks to any who are willing to share their expertise!

        • 1. Re: Trying to sort 2 dimensions dynamically
          Jim Wahl

          Hi Kristin,

           

          Two solutions come to mind.

           

          The first uses LOOKUP() table calcs for Top Reason (TR1), 2nd Top Reason (TR2), ...

          TR1 =

          IF FIRST() == 0 THEN

              LOOKUP(MIN([Reason]), 0)

          END

           

          FIRST() is used, since we only want one value returned (not one / row in the partition). Since you can specify the sort order, LOOKUP(..., 0) returns the first row and LOOKUP(..., 1) returns the second.

           

          Drag Customers to the Columns shelf, Reason to the Detail button, and TR1 to the Text button. Once added, you'll need to adjust compute using. Right-click TR1 > Edit Table Calculation. Compute Using Advanced. Move Customer and Reason the the Addressing side and Sort to Measure, Sum, Descending.

          2013-11-14 11-05-08.png

          Click OK and then in the Table Calculation dialog box, select Restarting every: Customer.

           

          Repeat this for TR2, but change the lookup index to 1: LOOKUP(MIN(Reason), 1).

           

          Lastly from the menu bar, select: Analysis > Stack Marks > Off.

          2013-11-14 11-49-54.png

           

          The problem is that these are not independent rows and you don't have row labels. Above, I "hacked" this by using a calculated field Top Reason Labels =

          "Top Reason" + CHAR(10) +  // CHAR(10) creates a new line

          "2nd Top Reason"

           

           

          Option 2: Modify the data source.

          To get an individual row per Top Reason, you need to run the LOOKUP(Calculation) for each member of a dimension that includes Top Reason, 2nd Top Reason, .... I did this by adding a Top Reason column to the data source and then creating an additional set of rows for each Top Reason. For example, if you want to display the two top reasons, you'll need to double the number of rows in your data set.

           

          You can do this with a custom SQL connection or by reshaping the data in Excel (create two new columns, then use the Tableau reshape tool). You can see what the result looks like in the embedded reshape.xlsx data source---you can see this by viewing the data source in Tableau or changing the .twbx extension to .zip  and looking in the /Data directory inside the zip. I used integers for the Top Reason (1, 2) to facilitate the LOOKUP function below.

           

          After creating the data source, you can drag Top Reason to the Rows shelf and create a new calculated field TR to find the most common reason:

          IF FIRST() == 0 THEN

              LOOKUP(MIN(Reason), MIN([Top Reason])-1)   // Top Reason is an integer integer starting with 1

          END

           

          Follow the Option 1 technique for Compute Using and Sort Order. Finally, right-click the 1 in the Top Reason column > Edit Alias and change this to Top Reason, 2nd Top Reason, ...

          2013-11-14 12-08-47.png

           

          If I've glossed over a couple of steps, just let me know where you're getting stuck. ...

           

          Jim

          1 of 1 people found this helpful