Hi Team,
Good day! Sorry for the question as I am new to google data studio.
We have this heatmap table with calculated fields. We are aiming to get the total number of occurences a text appeared in the 'Sub Assignee' column. Note that this column contains multiple values separated by semi colon:
However, we have no luck in finding the correct formula to get the expected results. Currently, what we have is:
CASE
WHEN REGEXP_MATCH(Sub Assignee, "Test Assignee 0") THEN 1
WHEN REGEXP_MATCH(Sub Assignee, "Test Assignee 1") THEN 1
WHEN REGEXP_MATCH(Sub Assignee, "Test Assignee 2") THEN 1
WHEN REGEXP_MATCH(Sub Assignee, "Test Assignee 3") THEN 1
WHEN REGEXP_MATCH(Sub Assignee, "Test Assignee 4") THEN 1
ELSE 0
END
It doesn't give the correct total of occurences. Assignee is the dimension we used:
We aim to tally the total number of occurrences of the Sub Assignee and place the total across the Assignee column. For example, Test Assignee 0 appeared 100 times in the Sub Assignee field, then it should put 100 in the column Total:
Is this possible? Greatly appreciate your help here.
Does anyone know the solution here? I would also like to learn how to do this for it will come in handy for our looker report! 🙂