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

Invalid formula on CASE WHEN when comparing two fields

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

Having trouble with 'invalid formula" when comparing 2 fields in a CASE WHEN statement:

 

The documentation states: "The operands on both sides of the comparison operators must of the same type: text, number or boolean. At least one operand must be a field, the other one can be a text, number, or boolean."

So when I read "AT LEAST", I assume using two fields in the comparison is valid, however when I try 

CASE WHEN location.lat=location.lng THEN "Oops" ELSE "Fine" END

I get "Invalid Formula".  The type of both fields is number. I tried to do the same with both fields being a text, but the result is the same. 

 

What am I doing wrong here? Can Data Studio not compare two fields in a calculated field? If that is the case, it should be mentioned more clearly in the documentation. 

Just "Invalid Formula' is INCREDIBLY cryptic.

 

1 Expert replyverified_user

Invalid formula on CASE WHEN when comparing two fields

Visitor ✭ ✭ ✭
# 2
Visitor ✭ ✭ ✭

I'm having the same problem. Seems like it should be simple to compare two fields. 

Invalid formula on CASE WHEN when comparing two fields

Follower ✭ ✭ ☆
# 3
Follower ✭ ✭ ☆

Having this issue as well. Wanted to do a comparison on when LandingPagePath = PagePath

 

(I have a good reason to want to do this ;-) )

Invalid formula on CASE WHEN when comparing two fields

Visitor ✭ ✭ ✭
# 4
Visitor ✭ ✭ ✭

I have the same issue. Have you found a workaround? Not being able to compare two fields make little sense...

Invalid formula on CASE WHEN when comparing two fields

Google Employee
# 5
Google Employee

Hi Tobias,

 

I'm sorry you're having trouble here. And I agree our error messages could be more helpful!

 

I think the problem is the aggregation of the fields you are comparing. You said location.lat and location.lng are both numbers, yes? Are they set to Number field type? If so, Data Studio is going to apply an aggregation to them (probably SUM). The problem here is that CASE operates on a record by record basis. But that doesn't make any sense when you are trying to apply it a sum.

 

If you change the Aggregation of location.lat and location.lng to None, it should work.

 

Hope this helps!

 

Rick

Invalid formula on CASE WHEN when comparing two fields

Visitor ✭ ✭ ✭
# 6
Visitor ✭ ✭ ✭

Hi Rick,

 

I am experiencing the same problem as everyone else in this thread. In fact, I tried copying and pasting the example formula directly from the 'About Case' help center article here: https://support.google.com/datastudio/answer/7020724?hl=en

 

Here is the code from the article:

CASE 
    WHEN Country IN ("USA,"Canada",Mexico")THEN "North America" 
    WHEN Country IN ("England,"France")THEN "Europe" 
    ELSE "Other" 
END

 

I copied and pasted the formula and I'm getting a 'Could not parse formula.' error. I don't seem to be able to get the THEN operator to work at all...

Invalid formula on CASE WHEN when comparing two fields

Visitor ✭ ✭ ✭
# 7
Visitor ✭ ✭ ✭

Hi Rick,

What about text fields? I'm trying to compare two text fields and it doesn't seem to be working.

Thank you,

Arnaud

Invalid formula on CASE WHEN when comparing two fields

Google Employee
# 8
Google Employee

Hi Ian,

 

My sincere apologies! There were punctuation errors in that example. Human error on my part, I'm afraid Smiley Sad

 

I've updated the documentation. 

 

The correct formula is

 

CASE WHEN Country IN ("USA","Canada","Mexico")THEN "North America" WHEN Country IN ("England","France")THEN "Europe" ELSE "Other" END

 

Please let me know if you still have problems.

 

Best,

 

Rick

Invalid formula on CASE WHEN when comparing two fields

Google Employee
# 9
Google Employee

Hi Arnaud,

 

You can compare text, and numbers as long as the aggregation makes sense.

 

What is your formula and what are the data types and aggregations of the fields involved? 

 

(Please note, due to the holiday weekend, my reply will be delayed until next week)

 

Best,

 

Rick

Invalid formula on CASE WHEN when comparing two fields

[ Edited ]
Visitor ✭ ✭ ✭
# 10
Visitor ✭ ✭ ✭

Hi Rick,

 

Here is the formula I'm trying to use:

CASE

WHEN Previous Page Path = Page THEN 'Refresh'

ELSE Default Channel Grouping

END

 

Previous Page Path and Page are both existing text fields, set up with aggregation "none".

 

Thank you,

Arnaud