I want to create buckets based on a measure in Looker. Can you help me write it ?

[danielkaye]

I'm trying to run this, but I'm getting an error because I can't create a dimension based on a measure. Can you help me fix this?

measure: sum_engagement_time_sec {
type: number
sql: ROUND(SUM(${TABLE}.engagement_time_sec),0) ;;
}

dimension: campaign_dashboard_sum_engagement_time_sec_buckets {
group_label: "Campaign Dashboard Specifics"
type: string
sql:
CASE
WHEN ${sum_engagement_time_sec} < 10 THEN '0-10 sec'
WHEN ${sum_engagement_time_sec} < 20 THEN '10-20 sec'
WHEN ${sum_engagement_time_sec} < 30 THEN '20-30 sec'
WHEN ${sum_engagement_time_sec} < 40 THEN '30-40 sec'
WHEN ${sum_engagement_time_sec} < 50 THEN '40-50 sec'
WHEN ${sum_engagement_time_sec} < 60 THEN '50-60 sec'
WHEN ${sum_engagement_time_sec} < 70 THEN '60-70 sec'
WHEN ${sum_engagement_time_sec} < 80 THEN '70-80 sec'
WHEN ${sum_engagement_time_sec} < 90 THEN '80-90 sec'
WHEN ${sum_engagement_time_sec} < 100 THEN '90-100 sec'
ELSE '100+ sec'
END ;;
}

[GavinW]

You need to create the bucketing dimension based on another dimension, then use a measure to count all the values that fall into those buckets. So you will need 'engagement_time' to be a dimension.

If you currently need to aggregate some fields to get your engagement_time, then you can turn it from a measure into a dimension by creating a derived table. In this new table, the measure value will just be a dimension that you can use to determine which bucket each line of data falls into and apply similar logic to that in your post.

There is another Community post that covers this here, and you can see it in our documentation here.