CODEWITHSUNDEEP
looker-studio
Ziyari
Ziyari

Reputation: 1

Google Data Studio - How to count entries within a range

I was wondering how, on google data studio, you would create a chart to find out how many entries within a specific column fall within a certain interval? For example, how many times in a specific column, there are numbers between "0 - 5".

In excel/google sheets, I'd use COUNTIF to do this, but unsure how to do this in Google Data Studio.

Would appreciate help with this!!

Thanks

Upvotes: 0

Views: 6644

Answers (1)

kstat
kstat

Reputation: 160

I've done something similar for this but it was for age group like 18-25, 26-34, etc. Here's what I recommend using Age as the number:

  1. Change the Age field (or any number field) to a string field
  2. Create a new field called Age Group (Or number group) by using the following formula:
CASE
  WHEN REGEXP_MATCH(Age, "[0-9]") THEN "0 - 9"
  WHEN REGEXP_MATCH(Age, "1[0-9]") THEN "10 - 19"
  WHEN REGEXP_MATCH(Age, "2[0-9]") THEN "20 - 29"
  WHEN REGEXP_MATCH(Age, "3[0-9]") THEN "30 - 39"
  WHEN REGEXP_MATCH(Age, "4[0-9]") THEN "40 - 49"
  WHEN REGEXP_MATCH(Age, "5[0-9]") THEN "50 - 59"
  WHEN REGEXP_MATCH(Age, "6[0-9]") THEN "60 - 69"
  WHEN REGEXP_MATCH(Age, "[7-9][0-9]") THEN "70+"
  ELSE "NULL"
END
  1. Create a column chart in a Data Studio report
  2. Make Age Group (or number group) your dimension
  3. Make Age (this should still be a string field) your metric
  4. Change your metric to COUNT

Comment below and let me know how this works!

Upvotes: 5

Related Questions