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.”
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.
Are you still looking for a solution ? Possibly if you can add an example and explain what you mean with other variables that will affect the visual, would be helpful.
We have not heard back from you regarding your question. We would still like to help. If we do not hear back in the next 3 days, we will archive the question