CODEWITHSUNDEEP
sqlgoogle-bigquerylooker-studio
Tom S
Tom S

Reputation: 39

CASE WHEN on Google Data Studio

Im trying to create a field on data studio that uses a "case when" and the function Date_diff. I keep getting the error "Could not parse formula".

  1. do I need to specify the metric of the difference between the two dates (as I do on BigQuery)?
  2. can I name the values with numbers, instead of strings (as possible to do on BigQuery)?
  3. Do I need to cast all fields to date format, or does Data Studio recognize them as dates by its own?
  4. Can I use the function "Between"?

My latest version (which isn't working) is the following:

case when date_diff(cast(checkin as date),order_date,day)>=0 and <=3 then 3

when date_diff(cast(checkin as date),order_date,day)>=4 and <=7 then 7

when date_diff(cast(checkin as date),order_date,day)>=8 and <=14 then 14

when date_diff(cast(checkin as date),order_date,day)>=15 and <=30 then 30

when date_diff(cast(checkin as date),order_date,day)>=31 and <=60 then 60

when date_diff(cast(checkin as date),order_date,day)>=61 and <=180 then 180

when date_diff(cast(checkin as date),order_date,day)>=181 and <=365 then 365

end

Upvotes: 1

Views: 2849

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

This is your condition:

(case when date_diff(cast(checkin as date), order_date, day) >= 0 and <= 3 then 3
 . . .

This doesn't make sense. You can express this as

(case when date_diff(cast(checkin as date), order_date, day) between 0 and 3 then 3

I would be more inclined to write this as:

(case when checkin >= order_date and checkin < date_add(order_date, interval 4 day)

You might need various casts to make this work, depending on the types of the underlying columns.

Upvotes: 2

Related Questions