Building Calculated Fields

Hey everybody, my name is Duncan. I'm a data engineer with West Loop Strategy, an AWS partner. Today for our AWS Analytics Expert Series, I'm going to be talking about how to make calculated fields in QuickSight.

Why would you want to create a calculated field on your Dataset? You already imported all the data and you feel good about the fields that you brought in. What ends up happening, and this has happened to me as well, is you think you have everything you need and then you realize you need something a little bit extra to tell the story that you want in your dashboard or your report. Or your boss asks you for something that's not readily available and you need to create it.

If you need to create a field in quicksight, you're going to use a calculated field. Calculated field just means that you're aggregating one field, you're adding two fields together, or you're trying to parse out some different attributes of your data to fill it out and make it a bit more digestible for your readers. So to do that, when you have QuickSight open, you can do it in two places. You can create it on the data prep level when you first imported your Dataset or you can do it on the analysis level.

I recommend trying to put as many calculated fields in the data prep level for optimization reasons and to make your dashboard load a little bit faster, but a couple on your analysis is not going to hurt. So from the analysis layer, I'm going to go to the top left and there's a button, plus add, you're going to click that and get a drop down menu. There's a lot of different things that you can add from here. For right now, we're just going to focus on a calculated field.

When this first opens, you're going to notice a couple of things. Add a name, you're going to want a title for your calculated field, and it needs to be unique. You can't copy another field, QuickSight won't allow that here. You're going to see the area where you're actually going to type out your calculated field, it has a little gray one at the top. For all of you SQL fans, this will look pretty familiar. On the right hand side, you're going to see another grouping of menus that shows fields at the top. If you click on that drop down, you're going to see a list of all your fields.

This is just a quick way to remember what's in there and what you already have so that you know what you're trying to bring together. Under that is parameters. We'll cover that in another video, and then under that you're going to see functions. Functions are all the different types of calculations that you can create in QuickSight. Cool thing about this is you can mix and match these, use them in different ways. If you're looking for something specific, you can search it.

Let's say you're not 100% sure what you want, but maybe something sounds like it's right. When you click on something like parseDate(), you're going to see that it gives you the syntax as well as a brief description. Amazon was really nice, and they put a learn more link. If you click that, it's going to take you to their documentation which gives you a full breakdown of how to use that function. So coming back to QuickSight here, let's say for this example, I have a company that does sales globally and I wanna see sales from a specific segment in a specific region and I don't want anything else.

What I can use is something called an ifelse statement. I use these all the time, they're very handy. Something to have in your back pocket. So what I'm gonna do is type ifelse and right there at the beginning, just when I type in, you're going see that a bunch of different options come up. The nice thing about QuickSight is it's gonna give you some suggestions. Ifelse is at the top and you're going to see it labels the functions. Some of my fields have come up that will come up later for us. I'm gonna keep typing ifelse and I can just click, enter and it opens the parenthesis for us.

Next, I'm gonna type in the first field that I want to determine which is segment. All you have to do is type segment and it already brings it up for you. And I want to say segment, I only really want to see sales from the enterprise. So that is gonna be a string within the segment field. To do that, I need to do apostrophe, and this is case sensitive, so if enterprise is capitalized in my field, it also needs to be capitalized when I type it in here. So I'm going to type in enterprise and then close that parenthesis.

I'm gonna do the same for my region as before. But before I do that, I need to delineate a new part of my ifelse statement. So I'm going to do and and I'm going to start typing region equals apostrophe. And again, I want just from the American region but this is case sensitive. So I'm gonna type in AMER exactly as it appears in my field. That's super important. It's caused me some headaches in the past. So just make sure that you're following up on that.

I'm going to do a comma because I'm gonna say we're starting the next part of our ifelse statements. So if these two things before are true, then show me sales if they aren't true, show me null. Excellent. So now I feel pretty confident in my syntax.
But if you maybe made a mistake as soon as you hit save, it's gonna pop up an error for you and you can follow it along to figure out what you need to do differently. I'm confident this is going to work. So I'm gonna hit save and it brings me back to my analysis layer.

What you can do to make sure that it did save is on the left hand side where you have your Dataset menu, you can search for whatever you named it or scroll through and find it. And now you're ready to add it to a visual.

Thanks for joining me today. To find out more about how we help our clients use AWS cloud technology. Check out our website, follow us on linkedin.