ZN Function in Tableau

0:00Hey it's Tim here. In today's video we're

0:02talking about the ZN function. Now the ZN

0:04function is an

0:05interesting number function because it

0:07allows us to handle nulls whenever we come

0:09across it in a

0:10data set. In today's video I'm going to be

0:12showing you exactly how that works. I've

0:14created a mock

0:15data set that I'll leave a link to in the

0:16description below so you can download that

0:18and use it along with this video. All right

0:20let's get stuck here. Okay I've got a very

0:22simple data

0:23set here. I've got it in excel. It's very

0:25simple. It essentially just tries to

0:26simulate some cinematic

0:28excels in different countries. I've got the

0:30genre on the left, revenue in column B and

0:32in column C

0:33the country. You can see that I have some

0:35empty fields here and this is what the ZN

0:37function is

0:37going to help me deal with. You'll see why

0:39I need to deal with this in Tableau in a

0:41little bit.

0:42But essentially this is our data set. We're

0:43going to get stuck in. I'm going to leave a

0:45description

0:45to this in the description as I've already

0:47said so you can download it and follow

0:49along as you

0:50need to. Now you do need to close the excel

0:51file if you're going to connect to excel.

0:53It can cause

0:54issues when you're trying to connect to an

0:56excel file live whilst working with it

0:58inside of Tableau.

0:59Okay so I've gone ahead and closed that

1:00file. It's going to open up excel file. It

1:02's going to open up

1:03my ZN function data folder and we're just

1:05going to connect to the mock data. Let's go

1:07ahead and

1:08open that and see what happens. And the

1:10next step is essentially you get the data

1:12model window. This

1:13window here and because we don't have any

1:15fancy data prep to work with we can just

1:17hop right into

1:18sheet one by hitting the bottom left option

1:20here. Okay now we've got our data set and

1:22it's perfect.

1:23Let's go ahead and bring some countries in.

1:25So I'm just going to bring in some rows and

1:26you'll see

1:27that we have five countries here that we

1:29can work with and then I'm going to go

1:30ahead and bring in

1:31the revenue for each country and there we

1:33go we have a pretty much perfect data set.

1:36Now you

1:36probably really can't see anything wrong

1:38with this data at this level of aggregation

1:40because

1:40everything is fine. But what I'm going to

1:42do is I'm actually going to create a new

1:44sheet. I'm going

1:44to try and bring in all the information I

1:46need to make this sort of more compelling.

1:49So I'm going

1:49to bring country and I'm going to bring in

1:51genre and then I'm going to bring in the

1:53revenue. Okay

1:55and now you can start to see where the

1:56issues are. You can see that I have this

1:59particular item here

2:00that's empty and in this particular

2:02instance essentially what the zn functions

2:04allows us to do

2:05is to replace that with a zero. Okay so let

2:07me just show you how the function works.

2:09Let me open

2:10up a calculation window and just make this

2:12a little larger so you can all see. Okay so

2:14you

2:14just type in zn it's very very simple you

2:17can see that the automatic list comes up

2:20hit enter

2:21and then asks us for the expressions in

2:23this case it's revenue so I'm just going to

2:25go ahead and

2:25type that in there and you're pretty much

2:27good to go it's as easy as that to type

2:29this up. I normally

2:31add zn to the front of the field so I know

2:33that I'm using the zn function in this

2:35particular field

2:36so I'll just type in here zn revenue and

2:38now that we've done that it's always good

2:41just to validate

2:42your calculation make sure it works make

2:43sure you understand what's going on. In my

2:45previous video

2:46I showed you that you can actually expand

2:48this little area here on the right and if

2:50you click

2:50on the function it gives you a highlight

2:52and it also tells you what the expression

2:55does on the

2:55right hand side so let's just review that.

2:57Returns the expression if it is not null

2:59otherwise returns

3:01zero. Okay so let's hit okay and see what

3:03happens. Let's bring zn here and we're

3:06going to place that

3:07here into this box you can see that it

3:09highlights it for me I'm kind of semi using

3:11the show me

3:12feature here I have no choice in the matter

3:13I know I said don't you show me but I have

3:15no choice in

3:16this matter that's just what I call it when

3:18you do that okay and now you can see that

3:21it actually has

3:22a value 0.0 here which is really really

3:24important let me show you why. Okay let's

3:27say we're doing

3:28some really really pivotal calculations and

3:30we need things like averages and aggreg

3:32ations to work

3:33correctly. In in Tableau quite a lot of the

3:35time if you're doing calculations of

3:37functions and a

3:38null appears in the data set you might

3:40actually get an error and in those

3:42instances you don't want

3:43the error you want to be able to keep on

3:45computing and just assume that the value in

3:48null is actually

3:49zero because that's essentially what it is

3:51in computer terms nothing exists but in

3:53human terms

3:54that's just a value of zero. So basically

3:56what I'm going to do is I'm actually going

3:58to change

3:59one of these fields to be an average so

4:01this sum here you're going to change it to

4:02an average to

4:03see what we get let's go over here make

4:05this an average and you'll see that we get

4:07a list of

4:07values for France it's 7.5831 and so on and

4:11so forth. Now what's going on here is that

4:14if I

4:14actually go into the root data here just

4:16look at this a little second and just go

4:17into the full

4:18data look at what's actually going on you

4:20'll see that the revenue field that it's

4:22actually taken

4:23into consideration is this column here and

4:25you can see there's actually quite a few

4:27nulls in fact if I

4:29look down my whole data set there's

4:31actually enough nulls there to actually

4:34sway my average

4:35because if these values are just zero and

4:37the database of the system instead of

4:39entering the

4:39value of 0.00 just enters a null then our

4:42average is potentially wrong we could walk

4:45away thinking

4:45that our average ticket sales here is seven

4:48pounds fifty when in fact we're giving away

4:50free tickets

4:51and that needs to be taken into

4:52consideration. So let's go ahead and solve

4:55that problem let's close

4:56this and now what we're going to do we're

4:58going to bring this zn revenue item here

5:00and I'm actually

5:01going to drop it in the same square what

5:03that does is it creates a whole load of

5:05complicated

5:06items on the view it changes it to measure

5:08names measure values I'll talk about this

5:10in another

5:11video but essentially what we want to do is

5:14focus here where it says sum zn revenue and

5:17we want to

5:17change this to be an average and when we do

5:20that you'll notice that the average is

5:22different okay

5:24and this is because this average is taking

5:26into account the zero value sales that we

5:29've actually

5:29got which we had quite a few of so this is

5:31actually a really important function

5:33because

5:33sometimes when you're doing calculations if

5:36you don't understand how exactly that data

5:38has been

5:38brought into the data set you're working

5:40with you might not realize that you need to

5:42be using the

5:43zn function to help sort that out it's also

5:45a great way of cleaning up your data

5:47essentially

5:48sometimes you just don't want the the term

5:50null all over your data set you might just

5:52want it to

5:52be a clean set of data and so the zn

5:54function actually can help clear this up

5:57and you know

5:58the term the terminology of null is not it

6:00not common in most businesses null is

6:02actually quite

6:03a data centric term it's something that bi

6:05developers data warehouses know a lot about

6:07but actually your everyday individual when

6:09they see that in a dashboard might not

6:11necessarily

6:11have the data literacy to understand hey

6:14this is a value that just has nothing in it

6:17you kind of

6:17spend a lot of time explaining to someone

6:19that you know nothing and zero are not the

6:21same thing

6:21so to avoid all of that zn is your friend

6:24okay so that's pretty much an example of

6:27how to use it

6:28uh hopefully a good way of using it and

6:29hopefully you now see the importance of why

6:32you should be using it in your data set

6:33that's pretty much it this is a much

6:35shorter video than

6:36the previous one and if you enjoyed the

6:38video you know what to do otherwise i'll

6:39catch you in the

6:40next video