2 Replies Latest reply on May 14, 2018 9:46 AM by Brady Atomos

    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.