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

# How to sum most frequently occurring value by group

Hello,

I have a data set that looks like this:

IDCountry
1us
2us
2us
3other
3us
3other

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
2us

3

other

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)

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!

• ###### 1. Re: How to sum most frequently occurring value by group

Hi Kim

Create a calculated field

COUNTD(ID)

Use this calculated field in the view to get the desired result.

On top of the country dimension, create a group

Right Click on the dimension -> Create Group

• ###### 2. Re: How to sum most frequently occurring value by group

Hope this helps.

Nested LOD helps this case.

Thanks,

Shin

1 of 1 people found this helpful
• ###### 3. Re: How to sum most frequently occurring value by group

Here You go:

T|F calculated field is:

{fixed [ID],[Country] : count([ID])} ={fixed [ID] : max({fixed [ID],[Country] : count([ID])}) }

Put it on filter shelf and choose TRUE.

1 of 1 people found this helpful
• ###### 4. Re: How to sum most frequently occurring value by group

Wow, thanks for this and the helpful screenshots.

These nested LODs are tricky!

• ###### 5. Re: How to sum most frequently occurring value by group

HI Jason,

You are welcome.

Don't try to create the logic by one formula.

Create multiple calc fields step by step with understandable failed name.

That's the easier way to keep tracking.

Thanks,

Shin