5 Replies Latest reply on Feb 4, 2016 7:46 PM by Jonathan Drummey

    Rank Function

    Anne Dahlstrom

      I create a report that shows a list of cities, their revenue, and their nominal YOY revenue.  I want to sort descending by YOY revenue in order to see which cities are declining.  I keep getting an error that you cannot rank by a table calculation.  Is there a work around or solution to this? 

        • 1. Re: Rank Function
          Venu Babu Kolasani

          Do you have the sample workbook?

           

          Thanks,

          Venu

          • 2. Re: Rank Function
            Melissa Davenport

            I have the same questions. Need to show top or bottom 5 by YOY, but when ranking a table calc, the rank is all out of sorts.

            • 3. Re: Rank Function
              Jonathan Drummey

              Besides manual and alphanumeric sorts, we can only sort dimensions (from primary sources) based on regular aggregate measures from the primary source, so that excludes table calculations. To get a table calculation to sort we need to do one of the following:

               

              a) Set up a table calculation that returns the desired alphanumeric sort value for each discrete header pill that needs sorted. In the case of a sort descending on Sales, that means the negative of sales.

              b) Change the table calculation into a regular aggregate measure, for example by using LOD expressions.

              c) Change the table calculation into a blended aggregate where a self-blend is used to help get the equivalent table calculation result and then the blended aggregate is set up to return the desired alphanumeric sort.

               

              In the attached I set up a) table calc and b) regular aggregate. For a) I used a second table calculation that returns the -WINDOW_MAX([YoY Sales TC]) for every State, then put that pill to the left of the State pill in the view:

               

               

               

               

              For b) I used a Level of Detail Expression to get the latest year in the data returned as a record-level results. Then the Current Year Sales is IF YEAR([Date]) = [Latest Year] THEN [Sales] END and the Prior Year Sales is a variation on that. Finally the YoY Sales measure is ZN(SUM([Current Year Sales]))-ZN(SUM([Prior Year Sales])) and that can be directly used as a measure and sort the State dimension:

               

               

              Jonathan

              1 of 1 people found this helpful
              • 4. Re: Rank Function
                Melissa Davenport

                Thank you Jonathan, option B worked great.

                • 5. Re: Rank Function
                  Jonathan Drummey

                  You're welcome!

                   

                  On Thu, Feb 4, 2016 at 5:12 PM, Melissa Davenport <