2 Replies Latest reply on Apr 7, 2016 9:27 PM by Vijaya Kumar Reddy Chinnam

    Nested Sorting is not working on  Crosstab

    Vijaya Kumar Reddy Chinnam



      I have a cross table  as mentioned below. Here both Item Code and Item Type dimensions  have been sorted on sum(Items). But as i marked in the below image, few of the rows are not sorted based on Items.

      According to my understanding, default cross table behavior in this case should be like   'Item Type' in each 'Item Code' should be sorted based on items.


      Please find attached workbook. Can anybody suggest me, why it is not working as expected.



      Screen Shot 2016-04-07 at 2.12.33 pm.png

        • 1. Re: Nested Sorting is not working on  Crosstab
          Carl Slifer



          This is a pretty common misconception. The sort is working exactly as intended. But, let's discuss the intended method and why its different from our mental usage.


          Sorting occurs within each dimension, without respect to the other dimensions. We are not sorting each item type within each item code. We are sorting by all Item Types. This means that when you sorted by Item Type it looked through the entire database of all your F, G, M, and S and based on the entire data base it put G, M, S, F in thar order. It did not care that each item code partitioned the data.


          To get around this,

          1) drag another instance of Items onto the details shelf

          2) Create a table calculation using Rank

              a) Right click the New measure and navigate to quick table calculation

              b) Choose Rank

              c) Right click the measure again and this time navigate to 'compute using'

              d) Choose 'pane down'

          3) Right click the measure and convert it to discrete

          4) Drag this field between Item Code and Item Type on your rows shelf.

          5) Right click this field on the rows shelf and uncheck show header


          We sort left to right based on our rows shelf without regard to what came before it. In this case the Table calculation is using only each pane and the pane is determined by the field that came before it ITEM CODE, which you've sorted high to low. The Table calculation we are using is automatically sorted by datasource order and because it is an integer this is 1 through infinity. This works for us as it will now put our highest item on top and our lowest item on bottom for each and every pane, or rather for each and every item code. Finally we can remove the on item type as it is not doing anything anymore.



          Carl Slifer


          1 of 1 people found this helpful
          • 2. Re: Nested Sorting is not working on  Crosstab
            Vijaya Kumar Reddy Chinnam

            Thanks Carl for explaining the internals of tableau and workaround.