1 Reply Latest reply on Jul 18, 2016 7:55 PM by swaroop.gantela

    Filtering plus benchmarks

    Luke Nofsinger

      I am working on a dashboard that will behave like an Olympic Athlete Profile. I have added a filter for Athlete Name so you can select a desired athlete. From here I have included a cascading filter for Event (100 backstroke, 100 butterfly, etc.). However I want the graph to show the athletes times in comparison to 3 benchmark athletes. My Benchmarks are the the #1, #3 and #8 athletes (world rankings) for that event. The End result should look like this:

       

      Screen Shot 2016-07-15 at 4.40.56 PM.png

      In order to produce the graph above, I had to change the filter type on Athlete to be multiple values (list) and manually select the 4 athletes (Desired athlete and 3 benchmark athletes).  However, this requires the user of the dashboard to know which benchmark athletes to select. I don't want to make that assumption. Ideally, the benchmark athletes would be auto selected (or populated) by the Event filter. However, I have no idea how to do this since I need the event filter to be a lower hierarchy than the athlete filter. Does anyone have any ideas on how I might find a workaround that would not involve creating hundreds of separate sheets?

       

      Thank you

        • 1. Re: Filtering plus benchmarks
          swaroop.gantela

          Luke,

           

          I'm not sure if this will work for your setup, but maybe it can be a first step.

           

          First, I joined the data set to itself in order to get all combinations of Athletes.

          Then, I flagged the benchmark atheletes in the duplicate set:

          IF [Rank (Sheet1$1)]=1 OR [Rank (Sheet1$1)]=3 OR [Rank (Sheet1$1)]=8

          THEN [Athelete (Sheet1$1)]

          END

           

          Then, I created a filter to get the benchmarks or the selected:

          [Athelete (Sheet1$1)]=[BenchmarkAthlete]

          OR [Athelete]=[Athelete (Sheet1$1)]

           

          Then I placed the Duplicate [Date...$1] on Columns,

          [Timing...$1] on Rows,

          [Athelete...$1] on Color,

          and ATTR([Athelete]) on the Detail shelf.

           

          To get the labels, I used a dual axis of Text.

           

          211220bench.png