Distribution of Metrics


I’m working on a simple categorization that’s needed in a dimension/measure but not able to do it as it’s a large table and several joins and nuances are involved in the same.

In my Explore, I have:

Team-Name Team-members
A 4
B 10
C 1

Now, I need to create buckets for Teams that have 1 member, 2-10 members and so on.

This can be done very simple by case-when statement and I already did it in PostgreSQL

select size_bucket, count(distinct team_id)
	(select distinct team_id, 
				 case when count(distinct member_id) = 1 then '1 member'
				 when count(distinct member_id) >= 2 and count(distinct member_id) < 11  then '2-10 members'
				 when count(distinct member_id) >= 11 and count(distinct member_id) < 50 then '11-50 members'
				 when count(distinct member_id) >= 50 then '50+ members'
				 else null
				 end size_bucket
	group by 1
	) a
group by 1; 

However, whenever I go about doing this in the LookML by “size_bucket” new dimension - I get errors and issues with Count(distinct ) of that ID as it cannot be done without errors.

I can’t declare the “size_bucket” as measure, as output is in String.

Kindly help me out in what part of the approach is wrong here and how I can get this basic SQL query working in LookML and Looker dashboards

This topic might help you:

1 Like