Looker Studio – Tricks on how to use the CASE formula

Hey, everyone. Ryan Bowse from SchoolBI here. Today’s tips and tricks, we’re gonna be looking at looker case statements.

We’ve graded this about a three out of five stars in the data artist level, meaning the difficulty.

The concept of case statements are not that complicated, but they can be very helpful. Work gets confusing or complex is really depending on how, involved you wanna make those case statements. So what we’ll be talking about today is really simple. What is a case statement and why and how you could use one?

A great example of this I have in my demo environment that I’m gonna show you in real time. So this is Richmond Academy. It’s fictitious data. It is becoming from a source data that’s coming into school BI, which is being presented here in Looker.

So all the data while is, ficticious, the flow of the data is real. So again, what is a case statement and why would you use it? So think of a case statement as something that you would do to a field to transform it to be something else. A very common example would be say you had a Google sheet and you had states with the two letter acronym.

And for whatever reason, you wanted to convert those two letter acronyms at the BI level, the business intelligence level, to a full name. So MA would become Massachusetts, you know, NY would become New York and so or vice versa. You had all the state, long names, and you wanted to convert them. But you don’t wanna go back to the source data and reconfigure all that.

So you wanna do something in looker or in your business intelligence system to actually make this easier for your, visualization story. So the example I’m gonna give is pretty straightforward. I have data coming in from the system telling me where my families live by City and County. What I wanna do is I actually wanna show, I have a requirement to show, which is Urban versus rural.

And I’m gonna show you kind of some tongue in cheek funny things that I can do here. But I’m gonna create a new field off that source data using a case statement. Okay? So if you look on the right hand side here, you’ll actually notice it’s probably small, but there’s no rural versus urban statement.

Right? It’s by of City and County. Okay. And I can actually create a new field, and that’s what I’m gonna show you to do.

But when I create a new field, that’s where I’m gonna use my case statement.

A case statement is very similar to a formula or function that you put into an Excel sheet or Google sheet. So it uses the same type of connotation cases of specific type of formula and function. Cases are oftentimes used when you have multiple statements. You want it to do this, then this, then this, then this. So I’m gonna show you very simple things, but you can envision it could get very complex. So the first thing I’m gonna do is I’m gonna name my new field that I’m just gonna happen to call it new location city versus Burbs, and I’m a call it calculated.

Now just to save you from the painful kind of experience of washing me type and get this right. I have this prewritten and I’m gonna paste it here. Now the really cool thing about looker is that if you wanted to write this from scratch, it actually starts to tell you down below whether you’re making mistakes. So if I wanted to write this case statement and I made some mistakes, you’ll actually see it turns red, and it’ll give you a little hint, a hint unexpected amperside there.

Right? It’s not expecting that. So think of a case statement as when something happens, then do this. And if that doesn’t help or doesn’t, transpire, what do you want me to do as an l statement?

Across everything else. If that isn’t correct, what do you want me to do? So this is a really simple one. What I’m saying is when the field city is equal to San Francisco, then make the new value city.

Everything else I want to stay city.

Now what you can do is you can actually change this if I don’t want that. Let’s say I wanted everybody in any city other than San Francisco to be a specific statement like rural.

So what I’m gonna do is I’m gonna do that. Okay? If San Francisco put Citi and if anything else put rural, I’m gonna hit save. When I hit save, what you’ll notice is I gotta hit done, and then it might be real small, but that field has come up now.

And it uses that calculated statement that I said at the end just for eye catching. That means I’m doing something. If you notice everything else is not using the word calculator at the end, that quickly lets me know that I’m doing something to this. Okay?

Now I’ve got this new field, but I need to actually change my graph. I wanna know how many people live in the urban environment, city versus rural. So what I’m gonna do is I’m gonna click this graph. I’m gonna click this, make sure it kinda populates on the right hand side.

I don’t really care about what filters it’s using yet. But do you see the dimension category? If you’ve been following my other videos, you know, dimension is the dimension that you want to show the data on. I’m gonna pull in the new field called new location city versus Burbs.

And what’s gonna happen is it’s going to change this donor graph, and I should get two values, city and rural. And there you go. So what happens now is it’s saying anybody in San Francisco is forty six percent. You’ll notice, I just wanna keep me honest here.

It says the same up top. Right? And then every other City is considered rural.

Now if I go back to that new city, field that we created using that case statement, you’ll see it’s pretty simple. Right? But what if I wanted to say that if you were in a particular city that you were in a different value I actually have something called when in point Ray’s already prewritten.

When in point Ray’s, we’re gonna say that you live in the Boondocks. Okay?

So what’s interesting here is I’m just putting another when. You can put ors and ands, but this is really straightforward.

When city equals San Francisco, then city, when city equals point ray station, then boondocks, everything else is gonna be called rural. I hit update, I hit done. And when I reload this, you’ll actually see that this will turn into three things. You might only have we might only have one person living in point raise, and I didn’t check my data to validate that.

But you get the idea. Right? So now you’ll see that this went down instead of sixty percent. It went down to fifty nine percent, and the boondocks is this little little bitty sliver in there, and he and they live out in the middle of nowhere.

So the reason why you might wanna use this is in areas where you need to transform the data on a particular graph or a particular reason. It’s a great way. It’s a lightweight way to do it. You’re not touching raw data.

You’re actually just moving the data into the visualization tool based on some constraints that you are looking to do. I say constraints and meaning the way you wanna show it. So that is a case function, and that’s how you use it. I will make one other kind of best practice suggest And that is is that we internally will document these case statements so that we can reuse them later.

Because sometimes you might forget how you actually wrote this. So I keep an internal wiki page, which I will say, hey, I wanna con a case statement to convert Massachusetts to MA, and because I may use that again some other time. So I create a little bit of a library of case statements. I highly recommend that share it with your team and the like.

I’d love to hear from you on how you’re actually gonna use case statements. And I thank you for being on this. We’ve got tons of resources, tons of new tips and tricks. This was a long one.

But hopefully you’ll enjoy.