Number of Occurrence of a Substring in Tableau

Question

I have a column with some words separated by comma. I would like to count the number of occurrences of each word like this -

animals      count_cats  count_dogs
cats             1           0
cats,dogs        1           1
dogs,cats,dogs   1           2

How do I achieve this in Tableau?

I created a calculated field following the instructions here - https://kb.tableau.com/articles/howto/counting-occurrences-of-a-string-within-a-dimension - but that would return either 1 or 0 since the CONTAINS function returns True or False.

Thanks in advance.

Answer 1

You can do like this.

For count_dogs use this calculation instead

(LEN([Animals])-
LEN(REPLACE([Animals], 'dogs', '')))/
LEN('dogs')

You can also do a parameteric count, by using the following calculation (param is paramaeter)

(LEN([Animals])-
LEN(REPLACE([Animals], [param], '')))/
LEN([param])

OR