2 Replies Latest reply on Feb 4, 2019 4:56 AM by Jim Dehner

    Use Multiple Rank Result as static/fixed value for future analysis

    Marcel Davidse

      I am creating a dashboard that's based off of ranking across multiple categories. I have attached a sample workbook with sample data below.

      I am trying to do a couple of things:

      1. From a data source, create a "static/fixed", i.e. a result of a calculation that will not be affected by filtering.
      2. Aggregate these ranks and create a new "rank" (i.e. overall/aggregate rank)
      3. A bit like 1, have my tool tip hover over the overall rank and not re-rank everything within the viz (i.e. everything gets ranked "1")
      4. In a highlight table, have any blank/NULL cells be coloured blank, rather than a colour such as green.

       

      I have tried Index functions, the lookup(attr[expression],0)/window_min(attr[expression]) workaround, but nothing seems to have helped me get to where I want to be; especially with multiple measures.

       

      Any help will be much appreciated!

        • 1. Re: Use Multiple Rank Result as static/fixed value for future analysis
          Hari Ankem

          Doesn't this look right?

          1.png

          What you needed to do was correct the formula for the overall rank, and also specify how the ranking is to be computed for each of the individual ranks being referred in the overall formula.

          1.png

           

          After you specify how the rank is to be computed for the Overall Rank, you should click on the triangle beside the "Overall Rank" text. You will then see the options for selecting how the rank should be computed for the Rank Frequency A, B and Patients as seeing in the 2nd image below. You can then set the ranks for each of the individual items as shown below for the Rank Frequency A.

          1.png   1.png1.png

           

          Hope this helps. Updated workbook is attached.

           

          Edited 2/4: Sorry, I didn't mention that because the data is getting filtered out, your rank will not work in the tooltip and shows up all 1. So, I had modified the tooltip to display all the values. And if you do not want the nulls to also show up as green color, you may want to use discrete values and assign colors. I have now assigned the white color to the null value.

          1.png

          • 2. Re: Use Multiple Rank Result as static/fixed value for future analysis
            Jim Dehner

            Good morning

            Not certain I fully understand the goal here

            You can create a ranking based on the total but I think yo want to hover over a value and see in the tool tip a filtered version of another sheet - i.e only see Bob but also see Bob's rank?

             

            Unfortunately that is not going work - Rank (in all its form) is a Table calculation that happens last in the order of operations - after all filtering has taken place

            The table calculation operates on the underlying table unique to each sheet - so your main sheet that includes all the people and Rank can use all the dat to rank each person

            when you pass the person's name to the second sheet - that sheet is filtered by the name leaving only a single person in the filtered table - so Rank returns 1

             

            sorry

            Jim

            If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.