Data Studio
6K members online now
6K members online now
Ask questions about Data Studio
Guide Me
star_border
Reply

Problem with CASE statement

Follower ✭ ✭ ✭
# 1
Follower ✭ ✭ ✭

I am experiencing a problem with the CASE statment in the Data Studio function.

 

Background: I have data from a number of countries, which are reported with the dimensions "ga:country" and "ga:continent"

 

I need to handle Japan individually as a country, while handling all other countries as only belonging to a certain continent only. To be able to chart or tabulate that, I have created a function "

CASE WHEN ga:country  = 'Japan' THEN ga:country ELSE ga:continent END

This computed field parses fine, but when I try to use it, I get either "Japan" or "     " (empty string) as a result. it appears that ga:continent used in this context simply evaluates to "all countries except Japan" (that's what the figures show at least), but I expected the case of "not japan" to be treated exactly as if I had only used "ga:continent" in the first place.

 

It might be the case that this is a limitation of how the "CASE function is implemented, but from a usage point of view it is an unnatural limitation which makes it impossible to handle the data in a useful manner. Please improve.

Problem with CASE statement

[ Edited ]
Follower ✭ ✭ ✭
# 2
Follower ✭ ✭ ✭

Hi Kjell,

 

You're exactly right about it being a limitation of the CASE function. The root of the problem is the the CASE function can only return boolean, text, or number values -- not metric fields like we're asking it to do here. (See "Notes" section at the very bottom of the article)

 

I hope they fix this soon because this would be easy enough to do with even the query() function in Sheets, but in the meantime, one manual work around I think would work:

 

1) Manually pull a custom report in analytics like so: 

Primary dimension: Continent

Secondary dimension: Country Code

Metrics: {{metrics you need in report}}

Advanced Filter: Exclude > Country Code > {{Japan's country code}} 

 

2) Export to Google Sheet (All results will be on Sheet1 by default)

 

3) Create 2nd tab (Sheet2) and import all unique continents from Column A of Sheet1 into Column A of Sheet2 via UNIQUE('Sheet1!A:A)

 

4) In Column B of Sheet 2, write the formula join("|",filter('Sheet1!B2:B{eod},'Sheet1!A2:A{eod}=A2)). This will result in something like this: 

Sheet2, A2: North America / Sheet2, B2: 23423|235235|235235

 

5) Create your new case function with all of the data from Sheet2

 

CASE WHEN REGEXP_MATCH(Country ISO Code, "23423|235235|235235") THEN "North America".....[repeat for every continent].... WHEN REGEXP_MATCH(Country ISO Code, "{Japan's Code}") THEN "Japan" ELSE "Uncategorized" END. 

 

6) If new countries come in as 'Uncategorized' you can always find out their names / continents and add them to existing CASE function

 

 

Hope this helps!!