Looker Studio – Tips and Tricks on custom sort order

Hi, everyone. Ryan Bowse from SchoolBI here today. And today, we’re talking about looker studio tips and trick We’re gonna unravel the mysteries of sort order quirks.

Now looker studio is an incredible tool, but let’s face it. It does have some quirks. If you haven’t stumbled upon this nuance yet, trust me, you will. Soon or later, you might find yourself scratching your head, or like some, me, banging your head against the wall in frustration trying to create your own custom sort order using looker studio.

But fear not today, we’re gonna dive into the concept of custom sort orders and explore some workarounds to make it work for you.

By default, looker lets you sort your data in ascending or descending orders using numerical or alphabetical values.

Now that’s great. But what happens if you wanna make it more tailored to your needs and make a custom sort order of showing your data?

Maybe you’ve used other BI tools where this was a breeze where you could create custom start orders, sort orders. Unfortunately, looker studio doesn’t support it directly. But we do have the tricks, which I mentioned right up our sleeve, and I’m gonna show you how to do it.

Some folks have asked what kind of scenario would I want to do a custom sort order?

Well, envision you have a bar chart. And maybe you’re counting the number of students per grade. And your grades go from j k pre k, maybe even have early childhood all the way up to twelfth grade.

Can you imagine trying to sort in looker on that? It’s going to try to do alphabetical. It’s going to try to maybe do the numbers. It’s not gonna be in the order that you want it.

In other words, it won’t be in a logical order. This is where a function in looker studio comes into play. Are you ready? This is the hack.

I’m gonna introduce you to something called reg x underscore match. It’s a regular expression match function.

One of the things that reg max match is is it allows you to set a command in looker to actually transform that data into something else. So it sounds complicated But don’t worry. I’ll walk you through the basics. I’m gonna show it to you in real time with the dashboard that I have on my screen, and it will be easy once you get to know it.

Alright folks. Let’s dive in and turn this seemingly complex task of creating your own sort order and using reg x underscore match. To create the custom sort order that will help you meet your needs. Again, I’m Ryan Biles from SchoolBI, and let’s get into it.

Alright. What you’re seeing right now on your screen is you’re actually viewing a fake school Richmond Academy where I have a bar chart showing the number of families from each one of these cities.

Now if you’ve, watched my prior video, you know that all of the data here is fictitious. And ultimately, there might be some gaps in the data that I show. So just bear with me. The table and the graph are essentially the same thing.

I’m limiting it to five, rows at the bottom and five rows at the top. Five columns. I’m sorry, of bars at the top. You’ll notice over here I have this sort order, and it’s by ID.

It’s by the count of the number of people. The user IDs associate. And all I have is descending and ascending. Right?

So it’s showing me the biggest number with descending, smaller number, lower number, blah blah blah. Now let’s just say for whatever reason, I mentioned earlier about putting a custom order in. Let’s say I actually wanted to put a custom order in, and I wanted to change how this was oriented. Maybe I wanted Tiburon to go first.

Maybe I wanted San Rafael to go first and and so on and so on. So one of the things you can actually do here is within Licker Studio, you can create a new field and sort on that. So think of it this way. What I’m going to do is I’m going to give my sort order, the sort order that I want it to look like, is going to have a city.

It’s gonna be to one, another city will be equal to two, three, four, and five. And what this will do is I will put them corresponding in order and then I will sort them on that. Now, just to save time, I’ve created this already. It’s called the sort field calculated.

I click on that and I’ll actually show you what I did.

Here is a case statement. If you watch my prior, videos and tips and tricks, ultimately what we what a case statement is is a way for you to write if when statements. So when this then do this. Right?

So, what I did was is I classified this with this reg underscore match. I mentioned that earlier. And what I’m saying is when if you see city, the field city, have an exact match regular expression match of San Francisco, then create a column field with five. So that’s gonna be my highest number in this example.

Then the next statement says pretty much the same thing, but it says if you see Ross, then give it a value of four.

Red Jack City Mill Valley, then three, and so on Tiburon two in San ensemble one. And then everything else give a zero.

Okay? That means any other there are other values in here. It’s not just these conveniently, these five or five cities, I everything else will get a zero. So it’ll be at the bottom of the list.

Now you’ll notice I’ve worked through this before. If you haven’t seen it, you should definitely watch my my other videos on how to create a case calculated fields here. And the idea is that, you’ll see this green button. If I make a mistake and put in something here that’s not right, you’ll actually see I’ll get a kind of an error and it’ll tell me which line it is.

Well, I know that error is because I have this unexpected, statement here. Now the good news about this is that you can create multiple different versions of when, you know, kind of when this happens, do this. You can nest them. You can make them very complex.

You can just cut and paste this and reuse it sixteen different times if you’d like. So again, what I’m actually doing is I’m creating a new field in my dataset that will be calculating these so that I can sort them in a minute. So when I hit done, what you’ll see here now is I’ve got this sort field calculated.

And what I’m gonna do is I’m actually gonna change the order. Now if you look, I’ll go back just to highlight this. I essentially wanna sort at San Francisco, Ross, Mill Valley, Tiburon, and San ensemble. Now all the other cities I don’t care about, you’ll notice these are not enough medical order. They’re in the order that I want them, and I associated the order by putting five four three two one. Okay?

So what I’m gonna do now is I’m gonna take this exact same graph, counting the number of folks that come from that particular city, and I’m going to then go into the sort field. And instead of sorting it by the count of the user ID, I’m going to actually sort it on the sort fields calculated.

Now what’s really interesting about this is that it technically is going to try to change the way I count it. I know I need to actually move it to average. Otherwise, I’ll get an error. So I’m just changing that.

So what’ll happen now is I should get that order. It will change. You’ll see it. Yep.

There it is. It’s not ordered by the number or alphabetical. It’s ordered because I’m telling it to order in a particular way. Again, down below, you’ll see this is the order based off of number of user IDs.

This can be hugely helpful for things that you might need to show in logical order, not in alphabetical or new, like, a numeric order. I’ll go back to that grade example. Imagine if you had, you had a graph of showing your enrollment by grade level. You have twelve grades.

If not fourteen grades because you have three k, t k, j k, you can envision you’re gonna want it to start on the left hand side maybe with the youngest. Go to the oldest. Maybe you use the term ninth. You spell the word out instead of using nine.

So there’s all these things that you can then now do and create a sort order that makes sense to you. Now there are other BI tools out there that you don’t have to do this fun formulaic expression. There’s ways to actually control the sort order right in the UI. Unfortunately, looker studio doesn’t do that today.

This is a great hack to get around that It’s a great hack to show real relevant data to your consumers or to your audience that’s gonna resonate with them. If you have any questions, I’ll put some more information in the links, If you have any questions, don’t hesitate to reach out, it’s been great to chat, and thanks for being online.