6 Replies Latest reply on Apr 2, 2018 10:58 AM by Ethan Elias

    Index() not sorting correctly, need help dynamic ranking/sorting across 2 fields!

    Ethan Elias



      I have a dataset that shows ~20 products and their numbers of installs in countries around the world.  I'm trying to create a calculated field that will rank the products within each country, so that I can determine if there are any products that have suspiciously high presences in a given country, compared to the other products in that country.  I can't post the data for sensitivity reasons.


      Using an Index function, I can properly rank the product/country pairs, but for some reason they won't sort correctly.  This causes problems with my function that compares each row to next ranked row (ie show me the difference between #1 and #2, #2 and #3, etc)

      That function, called DiffNext, is as follows: ZN(SUM([Number of Records])) - LOOKUP(ZN(SUM([Number of Records])), 1)


      You can see in the pic below how the Index is ranking the Number of Records correctly within a country, but no matter what I try I can't get it to sort in numerical ascending order



      Any idea on how to fix the sort issue?  I don't love this approach in general, and I'm not yet sure if it will work when I apply filters on top of the index rank.  Any other ideas are welcome, thanks!