2 Replies Latest reply on Jan 22, 2014 4:20 AM by Wilson Becker

    Index() based on another filter/index()?

    Wilson Becker


      Hey everybody-


      I'd really appreciate some direction on this one. See mock-up data attachecked below

       

      Tab 1 is a map; tab 2 is a table

       

      Both tabs have two filters: "Transactions Ranking" and "Success Ratio Ranking" .. both are table calculations with INDEX() used to rank the Transactions and Success Ratio fields

       

      Here is the ultimate goal: I want the user to be able to narrow his data by the first filter, "Transactions Ranking," which ranks zip codes by transaction volume. After the user has set a Transactions Ranking threshold, I want him to be able to set a "Success Ranking" threshold -- i.e., I want the Success Ranking to recalculate based on the user selected Transactions Ranking. So if the user wants to see the top 10 zip codes by Transaction, I'd like the user to see Success Rankings for the ten new zip codes *only*

       

      The problem I'm running into is that they seem independent of eachother, which makes sense. I have tried every combination of 'advanced filter' options, but to no avail..

       

       

      Can you all think of a way to make the "Success Ranking" recalculate every time the "Transactions Ranking" is adjusted?

       

      I'd appreciate any help!

        • 1. Re: Index() based on another filter/index()?
          Ron Chipman

          Shot in the dark, but did you try putting your first filter as a context filter?  I think this would work

          1 of 1 people found this helpful
          • 2. Re: Index() based on another filter/index()?
            Wilson Becker

            That's brilliant -- hadn't tried it, but it looks like context filters don't work on table calcs .. so since index() is a table calc, it wouldn't allow me to add the first filter into context. But I was not aware of the context filter in the first place! Very helpful

             

            The conclusion was to turn the first index() into a parameter with an integer range. Then I set up a filter for the second index() with the TOP function.

             

            The syntax was "Top [first param] by [first param's measurement] [aggregation type]"

             

            I think my question was a little too specific, but that context filter thing will be my ace in the hole next round

             

            Thanks, Tom