CODEWITHSUNDEEP
sqllooker-studio
okiedokie
okiedokie

Reputation: 11

Combine Metric and Dimension In Formula (Datastudio)

I am trying to figure out how to create a column where...

clicks * (CASE
  When Date <= "Jan 1,  2020" then 5
  when Date >= "Jan 2, 2020" then 10
end)

But the error I am getting is this:

Sorry, calculated fields can't mix metrics (aggregated values) and dimensions (non-aggregated values). Please check the aggregation types of the fields used in this formula

Date is the dimension and clicks are the metric.

What the result should look like:

    DATE ----- CLICKS --------- RESULT
Jan 1, 2020     100              500
Jan 1, 2020      40              200
Jan 1, 2020      10               50
Jan 2, 2020      30              300
Jan 1, 2020      90              900

Is there a way to change Date into a Metric, or is there another way to approach this problem?

Upvotes: 1

Views: 4599

Answers (3)

Donald Draefus
Donald Draefus

Reputation: 1

  clicks * MAX(
     (CASE
      WHEN Date <= "Aug 15,  2021" then 2
      WHEN Date > "Aug 15, 2021" then 4
      END))

This worked!

Upvotes: 0

yiah
yiah

Reputation: 81

If you wrap the CASE inside a MAX you'll get back a number and you'll avoid the error.

    clicks * MAX(
     (CASE
      WHEN Date <= "Jan 1,  2020" then 5
      WHEN Date >= "Jan 2, 2020" then 10
      END))

Upvotes: 1

Fauzan
Fauzan

Reputation: 1

I think the way the case statement is written is fine. You may need to check the data type for the Date dimension, and it should be in the "Date" format so that data studio knows to treat that data as a Date and then write the date in the case statement in the same format as well.

Upvotes: 0

Related Questions