2 Replies Latest reply on Jun 9, 2017 6:46 AM by Kenny Power

    How to sort a parameter in a region instead of entire data set?

    Kenny Power

      I have data that is in the format of:

       

      y, x, language, region, country. Example data:

       

      -91.51308, 36.970298,en,Illinois,United States

      55.741488, 24.851766,ar,رأس الخيمة,الامارات العربية المتحدة

      -85.605166, 30.355644,en,Georgia,United States

      -5.031181, 41.757845,es,Palencia,España

      35.354165, 38.955924,en,Felahiye,Turquía

       

      I am plotting this on a symbol map. I am using "Language" as a parameter to color the dots and differentiate the dots from each other, and using region along with x, y to plot the dots.

       

      I want the dots to show the color of the language with the highest count of that particular language in the region. This seems to be a bit more difficult to do then simply playing with simple filters or sorting (or maybe I'm missing something).

       

      The problem arises because when I try to sort by the count of a language, it takes the count of the ENTIRE data set instead of in the data in the region, displaying incorrect results.

       

      I think I may need to use window functions, but I'm not sure and I can't really figure out the correct function to use here.

       

      How do I sort the languages within the region without sorting it by the whole data set?

       

      Thanks!

       

      EDIT: Added a workbook