I have a data set that has students listed with their student ID. The students are entered into this data set any time a member of staff has an encounter with them.
Some of these students are listed once, some are listed anywhere between 2 and 17 times. e.g. they have repeat encounters.
On the X axis I want to display “number of encounters” e.g. 2 encounters, 3 encounters etc…
And on the Y axis I want to display the “count of students who have that number of encounters”. e.g. 3 students have had 12 encounters.
My data set has a ticket number, time stamp, student ID, a couple other identifiers including grade and age.
I have included an image of where I am. You can see the X axis is the individual student ID, and there are distinct groupings of ID that have the same amount of encounters.
I honestly can’t recall ALL the things I have tried but from what I can tell Looker may not be able to easily group or display the “number of unique encounters greater than 2 in buckets”.
In Looker, you’ll first need to aggregate your data by student ID to get the total count of encounters per student, and then re-aggregate that result to group by the encounter count. The simplest way is to create a derived table (either in LookML or using an Explore with a custom SQL) that selects student_id and COUNT(ticket_number) as encounter_count, then group that result by encounter_count to get the number of students per bucket. Once you have that summarized dataset, you can set encounter_count as your X axis and the count of students as your Y axis in your visualization. If you’re working only in the Explore UI, consider using a Table Calculation to bucket encounter counts, but for large datasets a derived table will be more performant.
