Data Studio
5.3K members online now
5.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...