How to write a complex calculated field in Data Studio with regex?

Question

I have been trying to make this regular expression REGEX filter work in Google Data Studio. It is supposed to do the following

Check the field "src_id" and COUNT all the values containing "widget".
Check the field "Page" and COUNT all the values starting with a "/" and ending with "/start".
Check the field "real_title" and NOT COUNT any value containing "-".

I have tried using the code below but it's not providing the correct result:

COUNT(CASE WHEN REGEXP_MATCH(src_id, "^widget" ) THEN 1 
WHEN REGEXP_MATCH(Page, ".*(/start)$") then 1 
WHEN REGEXP_MATCH(real_title, "^[^-]") then 1  
ELSE 0 END)

I expect the result to "52" but it's giving me "582. I need help to spot what I'm doing wrong.

Could you elaborate by providing a publicly editable Google Data Studio Report (additionally, a Google Sheet if it's the data set) of the scenario (using sample data that shows 1) Input values (~10 rows) 2) Expected output 3) An attempt at solving the issue)? It would help users visualise the issue and test out suggestions on a specific use case with objective right / wrong answers. Without a [Minimal Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example) it would be difficult to pinpoint a suggestion and the issue, e.g. Data Set, Data Source, Report, Fields, Chart — Nimantha, Feb 15 '22 at 04:27

score -2 · Answer 1 · edited Sep 08 '19 at 07:35

The problem is your count() - it is counting all the entries including zeroes.
either use sum() or just use the case statement and sum where you want it .
Examples

TestField1

COUNT(CASE WHEN REGEXP_MATCH(Page Title , "^How.*" ) THEN 1 
ELSE 0 END)

This returns 58 - the number of page titles on my site.

TestField2

Sum(CASE WHEN REGEXP_MATCH(Page Title, "^How.*" ) THEN 1   
ELSE 0 END)

This returns 7 - the number of titles on the site that start with "How"

You really don't need the sum() function in most cases because you can sum the field in the places you need it.

How to write a complex calculated field in Data Studio with regex?

1 Answers1