AdWords is now Google Ads. Our new name reflects the full range of advertising options we offer across Search, Display, YouTube, and more. Learn more

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

Trouble with CASE statement in Calculated Fields

Follower ✭ ✭ ✭
# 1
Follower ✭ ✭ ✭

Once again, I am stuck with what seems like arbitrary limitations in the CASE statement for Calculated Fields.

 

In this case, I have monthly data for the whole year so far, but since the current month is not complete, I need to do some kind of estimate for the current month, based on sales so far in this month. Last month, I managed to hard-cod this to do something special for October, but come November, I thought I might do something more intelligent.

 

So, I now have a field, "Current month" (with the same value, "November", for all rows of data - which itself is pretty stupid, but necessary since there does not seem to be a concept of "global" values in Data Studio). I then want to compare that with the field containing the actual month for each row of data, to see if a particular row belongs to the current month or not. If I present the values of these fields in a Data Studio table, I can see that I get one column of all "November", and another one with "January", "February" and so on. 

 

BUT, if I then stick the values of these fields into a CASE statement, it seems I cannot compare these two fields; CASE accepts one field and one explicit constant (like "November") but not two fields which (potentially) both could vary. This seems like a completely arbitrary limitation (except that it might have been easy to implement, I guess).

 

Furthermore, there is something strange in that, if I actually enter "November" into a logical comparison with the field containing the month for a specific row, then I can return a metric field if that is true, but not when it is false (in the ELSE clause). Frankly, that does not make any sense at all...