3 Replies Latest reply on Sep 21, 2018 1:09 PM by Kyle MacKenzie

    Index Sorting with Hierarchies

    Brady Atomos

      Hi I would like to sort my table in a logical order while keeping my hierarchy functional.  I have been using the index calculation to sort it but collapsing my hierarchy causes issues. Any help would be greatly appreciated. 

        • 1. Re: Index Sorting with Hierarchies
          Okechukwu Ossai

          Table calcs don't often go well with multi-level hierarchies. A not-so-perfect solution is to use LOD expression. All the fields in the hierarchy should be included in the LOD. I've used Sample Superstore data as an example. See attached workbook.

           

          Create calculated field [Sort Value]

          Modify this to suit your dataset and use it to sort each field in the hierarchy in descending order. For example, I've sorted Country, Region, Category and Sub-Category one at a time using [Sort Value]. This works perfectly if you filter for example by Region. However, there could be small discrepancies when all regions are in the view. This is a compromise you may be ready to accept. I'm not sure there is any other way.

           

           

          See results at the different hierarchy level below

           

           

          Hope this helps.

          Ossai

          • 2. Re: Index Sorting with Hierarchies
            Brady Atomos

            I'm suspecting that I'm using the hierarchy inappropriately because my lowest level of granularity (Sub Category in your example) shares the same values as their counterparts. That stops your solution from working.

            I still want this breakdown, but I'm assuming my sorting options are limited.

            • 3. Re: Index Sorting with Hierarchies
              Kyle MacKenzie

              I have something very similar to Ossai visual.  I have a 7 level hierarchy but with many different sum values; Sales, Cost, margin.  If I have just the level 1 hierarchy displayed and I sum anything it works.  If I open the hierarchy it then reverts to sorting on Alpha on the Level 1 Hierarchy but the Level 2 is sorted by the sum value selected.  I would like the sort of the level 1 to remain and the level 2 to be like it is.  Is this possible.  What is crazy is a user showed me this on the Online version.  I downloaded the workbook and in desktop it works exactly as I want it to.  However in the Online version it doesn't.  I tried republishing it but it seems to make no difference. 

               

              Thanks,

              Kyle