CODEWITHSUNDEEP
categorieslooker-studiocalculated-field
nemja
nemja

Reputation: 499

Creating Calculated Fields in Google Datastudio

I would like to create categories based on a count of variable.

 CASE
      WHEN COUNT(variable) = 1 THEN "1"
      WHEN COUNT(variable) = 2 THEN "2"
      WHEN COUNT(variable) = 3 THEN "3"
      WHEN COUNT(variable) = 4 THEN "4"
      WHEN COUNT(variable) >= 5 THEN ">5"
    END

I get an error that says that my formula is not valid. However, I cannot see where the mistake is and Google does not offer help in this regard.

Upvotes: 0

Views: 673

Answers (1)

justbeez
justbeez

Reputation: 1387

This takes a little getting used to in Data Studio, but you can't use all functions inside of a CASE statement (as noted in the documentation).

Here's how you can work around this limitation:

  1. Create a new calculated field with the value of COUNT(variable)
  2. Set the new field's aggregation type to Sum in the field list
  3. Then create your CASE statement formula referencing that new field

If you don't want this extra field showing up in reports, you can disable it in the data source (it can still be used by your other formula).

Also note that the input of COUNT itself cannot be an aggregate value (e.g. result of SUM or a metric with the aggregation type set).

This is an incredibly frustrating bit of Data Studio, as you end up with a lot of these fields floating around and it adds an extra step. The unhelpful error message definitely doesn't help either.

Upvotes: 1

Related Questions