How to count specific values in a string field

Question & Answer
,
1

Hi, I’m looking to get the count of the value that’s most common/prevalent in a string field.

i have a field named “LOB,” containing several different values — Card, Audit, Risk, etc. Can I create a calculated field that produces the count of the value that has the highest count in the field?
For example, if the LOB shows Card 10 times, Audit 5, and Risk 2, I’d like this calculated field to show the number 10 (the count of the “Card” value as it is most frequent).

Also, is there a way to get the count of the second highest value as well? In the above example, the second highest would be 5.

Finally, in an insight visual, I’d like to also get the name of the two values with the highest count. In the above example, the function would produce “Card” and “Audit.”

Thank you!

2

Hi,

without testing it myself… Try this

Create a calculated field where you count your entries in LOB. use this field in a visual and add a top/bottom filter for the count in this visual.

Thomas

2 Likes
3

Thanks for your reply @thomask!
I’d like to use this calculated field within an insight narrative, which will contain other variables that can unfortunately be affected by the top/bottom filter you suggested. In other words, if I add a filter to the insight narrative to filter the “highest LOB count” variable, that filter will also (undesirably) affect the other variables in my narrative.