5 Replies Latest reply on Nov 23, 2018 10:34 AM by Shinichiro Murakami

    How to sum most frequently occurring value by group

    Jason Kim

      Hello,

       

      I have a data set that looks like this:

          

      IDCountry
      1canada
      1us
      1canada
      2us
      2us
      3other
      3us
      3other
      4canada


      I figured out a way to assign the most frequently occurring country to each unique ID using this filter/table calculation which I think is working as intended, but I'm not quite sure:

      IF COUNT([Country])=WINDOW_MAX(COUNT([Country])) THEN 1 END

       

      Which is supposed to create a view that looks like this:

       

      IDCountry
      1canada
      2us

      3

      other
      4canada

       

      How do I create a view that will return a sum of users from those countries? I expect the end result to look like this:

       

      CountryAmount (# of distinct IDs)
      canada2

      us

      1

      other

      1

       

      I believe I am really close to the solution using a combination of table calculations, filters, and LODs, but I am missing something.

       

      In SQL, this entire process would look something like:

       

      SELECT COUNTRY, COUNT(DISTINCT ID)

      FROM (SELECT COUNTRY, ID FROM DATABASE

      GROUP BY COUNTRY, ID

      ORDER BY COUNT(DISTINCT ID) DESC) TEMP

      WHERE TEMP.COUNTRY = COUNTRY

      GROUP BY COUNTRY

       

      Thanks for any help!