Tableau Desktop Coding Bootcamp | Logical Functions 2

Summary

In this video, Bawar walks through key concepts in Tableau, focusing on logical functions, handling null values, and writing calculations. He explains how to categorize data using IF and CASE statements, manage errors in real-time reports, and handle common issues like division by zero. The video also covers important techniques like using ZN and ISNULL functions to standardize data and avoid errors in Tableau calculations. Bawar encourages viewers to practice the discussed methods and invites them to explore the logic further by writing nested IF statements.

Takeaways

• 😀 The video is a part two session on coding and explores further Tableau calculations and logic building.
• 🖥️ The presenter demonstrates how to categorize data in Tableau using logical functions, such as categorizing based on profit values.
• 🚨 Exceptional handling is explained, focusing on how to deal with null values in real-time scenarios.
• 📝 The 'if null' and 'is null' functions are discussed as important tools to handle missing or null values in Tableau.
• 📅 A case statement is used to convert month numbers into month names, a common requirement in real-time scenarios.
• 🤔 The presenter suggests using multiple methods, like nested ifs, to achieve the same result for better understanding.
• 📊 Another use case is presented where subcategories are grouped into categories using a case statement.
• 🔢 The concept of handling division by zero errors in calculations is covered using an if-else condition to avoid null values.
• 📉 The ZN function is introduced as a way to replace null values with zero, a crucial function for standardizing Tableau code.
• ⚙️ The importance of functions like ABS (absolute value) to ensure code stability and handle specific requirements in calculations is discussed.

Q & A

What is the primary topic of the video?

The video is focused on coding logic development in Tableau, including handling null values, writing logical functions, and using conditional statements like 'IF', 'IS NULL', and 'CASE' for data categorization and calculations.

Why is handling null values important in real-time projects?

Handling null values is crucial because in real-time data, missing or undefined values can cause issues in calculations and reporting. By addressing null values, you can ensure your logic works smoothly, even when data is incomplete or unexpected.

What function does the speaker use to handle null values in Tableau?

The speaker uses the 'IFNULL' function to handle null values in Tableau, replacing nulls with a default value, such as 'Not Defined' or zero.

What is the purpose of the 'IS NULL' function mentioned in the video?

The 'IS NULL' function is used to check whether a given value is null or not. It returns a Boolean result: 'TRUE' if the value is null and 'FALSE' if the value is not null.

How does the speaker categorize profit values using conditional logic?

The speaker categorizes profit values using a conditional 'IF' statement. For example, if the sum of profit is greater than 40,000, the profit is categorized as 'Good Profit.'

What common interview question related to Tableau is discussed in the video?

A common interview question discussed in the video is how to write a case statement to categorize months or values in Tableau. This question checks the interviewee's logical ability to work with data in different formats.

How can you categorize months in Tableau based on numbers using a CASE statement?

To categorize months based on numbers using a CASE statement, you can map the numeric values (e.g., 1 for January, 2 for February) to their respective month names, allowing you to display month names instead of numbers in your Tableau visualizations.

What is the purpose of using a 'CASE' statement in the video?

The 'CASE' statement is used to categorize data into different groups based on conditions. For instance, the speaker uses it to categorize subcategories into different groups, like 'Group 1', 'Group 2', etc., based on subcategory values.

How does Tableau handle division by zero in calculations?

In the video, the speaker demonstrates handling division by zero by using a conditional 'IF' statement. If the denominator is zero, Tableau returns zero to avoid errors in calculations.

What function does the speaker recommend using to standardize code and handle null values?

The speaker recommends using the 'ZN' function in Tableau to standardize code. The 'ZN' function replaces null values with zero, ensuring that calculations can proceed without interruptions due to nulls.

Outlines

00:00

👋 Introduction and Recap of Part 1

The video begins with a greeting from the host, Al Bawar, and an introduction to his YouTube channel, Express Your Data. He briefly recaps the first part of the series, where viewers learned how to build logic and categorize data in Tableau. Al encourages new viewers to watch the first video and subscribe before continuing. He outlines that this video will cover advanced calculations in Tableau, focusing on building on previous lessons, handling data categorization, and writing more complex functions.

05:01

💡 Categorizing Profit Data in Tableau

In this section, the host introduces a logic-based calculation to categorize profits. He demonstrates how to assign values like 'Good Profit' if the profit is above a certain threshold (e.g., $40,000). He addresses handling null values that result from incomplete logic, introducing the concept of exceptional handling in real-time scenarios. Al explains the use of the `IFNULL` function to replace nulls with meaningful data like 'Not Defined' and compares this with the `ISNULL` function, which checks whether a value is null, returning a Boolean result.

10:02

🗓️ Using Case Statements to Display Month Names

This paragraph focuses on using a `CASE` statement to convert month numbers into readable month names. Al walks through the process of building this logic in Tableau, replacing month numbers with their corresponding names (e.g., 1 = 'Jan'). He highlights the importance of the `CASE` statement for data transformation and mentions how such tasks are often encountered in technical interviews. He wraps up by showing how to handle errors in logic, particularly when there is a mismatch in months, and invites viewers to practice writing similar logic using nested IF statements.

15:03

📊 Grouping Subcategories Using Case Statements

Al demonstrates how to group subcategories into different zones using a `CASE` statement. He groups subcategories (like Accessories, Appliances, etc.) into multiple groups, explaining how Tableau reads these subcategories and how to assign them into custom groups. This is a common task in real-world scenarios where users need to categorize data into specific zones or headers. The use case showcases how to logically organize and group data in Tableau through a step-by-step example.

20:05

🧮 Handling Division by Zero in Tableau

In this section, Al explains the challenge of handling division by zero errors in Tableau. He demonstrates how to avoid such errors by checking if the denominator is zero before performing any division. If the denominator is zero, Tableau should return 0; otherwise, it calculates the profit by dividing profit by sales. This is a crucial technique for real-time data handling where division by zero can cause errors or misleading results. Al also introduces the `ZN` (Zero Null) function to replace null values with zeros and standardize calculations.

Keywords

💡Tableau

Tableau is a popular data visualization tool used for transforming raw data into understandable graphical representations. In the video, the speaker demonstrates how to use Tableau for logical operations, categorizing data, and generating calculations. The key message revolves around understanding how Tableau processes and computes data step by step, such as how it handles null values and creates visualizations.

💡If-Null Function

The 'If-Null' function is a conditional statement in Tableau that allows users to handle null values within their data. It ensures that if a value returns null, a specified alternative value is displayed, preventing errors in the dataset. The speaker shows how to use 'If-Null' to handle unexpected data conditions, like when profits do not meet a predefined threshold.

💡Case Statement

The 'Case' statement in Tableau is used for conditional logic, allowing users to categorize data based on certain conditions. In the video, the speaker uses a case statement to transform numerical data (like month numbers) into readable names (like January, February) and to categorize subcategories into groups. This concept is essential for flexible data manipulation and efficient data presentation.

💡Subcategory

In Tableau, a subcategory refers to a smaller division of a larger dataset, often used for grouping or filtering data. The speaker demonstrates how subcategories can be sorted and categorized based on their profits or other metrics. For example, subcategories like 'Accessories' or 'Appliances' are grouped based on custom logic in the video.

💡Profit

Profit is the difference between the revenue generated from sales and the cost of the goods sold. In the video, the speaker categorizes different subcategories based on their profit levels. This key metric is used to create logical conditions, such as whether a product's profit is considered 'good' or falls below a certain threshold.

💡Null Values

Null values represent missing or undefined data in a dataset. In the video, the speaker emphasizes the importance of handling null values in Tableau, as they can cause errors or misrepresentations in reports. Methods such as 'If-Null' and 'Is-Null' are introduced to manage these values efficiently by either replacing them with default values or flagging them for further inspection.

💡Logical Functions

Logical functions in Tableau help define and execute conditions that influence how data is categorized and calculated. The speaker explains how to use logical functions like 'If-Else', 'If-Null', and 'Is-Null' to categorize data based on profits, handle errors, and ensure consistent data reporting. Logical functions are central to automating decisions in data analysis.

💡Is-Null Function

The 'Is-Null' function is used to check if a value in Tableau is null, returning a boolean (true/false) result. The speaker explains its importance in identifying where data might be missing or incomplete, helping users debug their calculations by distinguishing between valid and null data points.

💡String Values

String values refer to text data in Tableau. The video demonstrates how to handle string values when categorizing data or writing logical conditions, such as replacing null profit categories with a 'Not Defined' label. This concept is crucial for dealing with non-numeric data in visualizations and reports.

💡ZN Function

The ZN function in Tableau converts null values to zero, ensuring that numerical calculations do not break due to missing data. In the video, the speaker uses the ZN function to handle situations where division by zero might occur, ensuring the final output is standardized and prevents errors in calculations.

Highlights

Introduction to categorizing data and writing calculations in Tableau.

Explanation of how to use logical functions to handle data.

Tutorial on using 'if' statements to categorize data based on profit thresholds.

Introduction to handling null values with the 'ifnull' function.

Explanation of handling exceptional cases in data with null values.

Introduction to 'isnull' for determining whether a value is null in Tableau.

Use of 'case' statements to convert numerical month values into month names.

Demonstration of categorizing data into groups using 'case' statements.

How to handle division by zero in calculations.

Demonstration of using the ZN function to replace null values with zeros in calculations.

Introduction to standardizing code with the ZN function for real-time calculations.

Example of using Tableau's built-in functions to compute quick table calculations.

Explanation of using the absolute (ABS) function in Tableau for handling negative values.

Encouragement to write logic in multiple ways for better understanding and flexibility.

Concluding remarks on the importance of mastering different ways to handle data in Tableau for real-time application.

Transcripts

hello everyone this is all bawar and

welcome to my YouTube channel express

your data so we were you know learning a

lot in our coding sessions and this is

part two video where we will explore

what all we have learned till now and

we'll continue so if you are new to my

channel don't forget to like share and

subscribe and let us get started without

wasting much time

so if you have friends in my last video

video please do

watch that video so in that video we

have learned how to build logic from the

base and we have seen how the data is

getting categorized in Tableau and how

the execution you know is happening step

by step so I'm going to take it a

further level up and we are going to

write few more calculations and we'll

see how Tableau is trying to compute

that okay so for that again what I'm

doing is I'm taking my sub category and

I'm also taking my profit

for now because we have got all the

values in that and I'm just sorting it

now let us open one of the previously

built logic because I want to take some

part from that and uh okay

to

okay never mind we'll we'll write it so

I'm writing logical

functions

so it's day two for us okay now what I'm

doing is like we have categorized

earlier using the same I'm trying to

categorize uh it for some time uh for

some values on this so if

my sum of profit

okay is greater than uh let's say assume

I'm giving 40 000 okay then I want to

categorize as good profit

okay and without specifying end part or

else part I'm just ending my statement

here okay so now like we know it will

generate uh or create null values for us

right see here till here it is like this

now if at all you want to handle this

null so we call it as exceptional

handing

X

exceptional

handling how do you handle the null

values that are coming in your data one

of the very important you know uh

task in real time because most often

after writing your entire logic only for

some part of data we get this kind of

things okay so we need to think about

that also okay so now

we have categorized this as good profit

but we never expected that you know we

will have profits less than or in this

range now later on after moving your

report into production you got some

values which are not falling under your

condition then in that case what can we

do

so now

I want to handle this so I'm writing

simply

if

null

now observe here I am passing this

conditional statement to this if

statement so if my value is returning

null then specify as not defined

and I am simply closing it now what is

now what will happen first this inner

statement will get executed it will it

will print whatever the value is there

okay so in cases where it is null then

it will print as not not defined click

on apply

see now clearly if this kind of value is

uh we are getting in the calculation

then we'll know that okay this is coming

from this part and we need to fix this

okay so that is your if statement so

many times we use this if null in real

time as well now this is because string

I am deriving a string or defining a

string value here

now suppose you know instead of this

string value I want to uh return 0 in

cases of that so that is also one

possible case where you might get nulls

in number

okay so that will see uh and uh other

statement with respect to if null we

also have another statement which we can

use

is null so is null will return either

this statement is returning a null value

or not that's it okay so for this we

don't need

another condition so just what I'm doing

is uh

just

removing this

and removing or just keeping it as is

null

now e is null of our

original if statement that's it

now what will happen this is this will

become Boolean statement just it will

return either true or false so observe

now

if I see here false this means for these

three it is not it is giving us some

value so it is not null false means it

is not null

okay otherwise

it is

true which means for all other values

from papers it is returning

null value that is what it is trying to

tell us from this statement

okay so that is your if null and E is

null statement one of the very you know

important activity

of handling this now what we'll do is

we'll write a simple case statement okay

so if you remember in our previous video

we have uh derived some

values related to month same thing I'm

trying to do here and uh

let us keep this as is and uh

I will see if date related is there or

not okay so we have date name here I'll

just remove this and this

and I'll add it here

so I have assumed in your database you

are getting your months as this one two

three four till 12. now you got a

requirement that you know you have to

display month name

and not the number okay while there are

multiple ways of doing that I am just

doing it using a case statement just so

that we can understand how the statement

will work so now I'm just calling case

of

this date and name

so when I call that Tableau will try to

read all the values that are there in

this

okay now when

then

I want to print as

like say Jan

and I am and date

okay so this is valid now okay so

likewise I am writing it for all the

months here

two three four five six

okay I'll simply copy this

and paste it

okay let us uh

so I'm gonna write the numbers here to

three

four

five six so one common use case that we

might get in real time so you should be

aware of this logic as well it's

imported for us to understand so

likewise now I can write

my month name now I can use it wherever

I want so first of all I want to

use in number format or

string format whatever I want I can use

it

so July

so often this question is also asked in

interview many times I think in general

today I've asked I think in uh

Accenture also they have asked I think

in Amazon also they asked this kind of

you know question in preliminary around

so again the question is just to check

your logical ability okay so not in

terms of Tableau but technologically you

should be in a state that

you can derive this

okay I'm clicking on apply and I can

take this statement and I can drop it

now you see it is returning me

this value okay so now you got null here

see here

between some error is there at your 12th

number now you will go back and observe

what you have done so you will see that

you have written 11 12 times so this

kind of issues are also possible when

you are writing you need to First verify

if it is correct or not now you got all

the values

if any value you are missing at the

then

that will display null

yeah

Okay so

if anything other than this value that

you are coming then you can write simply

again if null on this

sorry

just to standardize the code

if null

of this entire statement and what you

want to tell not categorized

I'm writing it like this

and close it

sorry

like this

okay so this way also I can write it

click on apply

now again another use case uh we will

see so same thing and now you can try it

using if statement you can try to build

this using nestative and see if you are

able to you know achieve this again you

know it becomes interesting when you try

to write the same requirement in

multiple ways that is when we will be

able to understand the logical you know

part of it and we will be able to tune

ourselves so that we can write logic in

multiple ways so again the question for

you whoever is watching uh try to write

the same

thing in using IF

or I will say nested if

so that you understand better try it

that way if you are not getting let me

know I'll post it if you are getting it

do paste it in the comment section so

that you know it helps others who are

watching

now likewise you know one common

requirement we get in you know real time

is you know categorizing the values into

some different zones so assume here I've

got like 17 subcategory items now user

wanted to categorize into some groups

okay so one method is you know creating

a groups but I don't want to follow

group approach just because you know uh

you know

from learning perspective I want to use

some calculations so that I can derive

the value

so for that what I'm you writing is just

creating grouping uh sub category here

now here I want to group my

category into four parts or two to three

parts let us say as an example so again

many times I think I have asked in

interview but many failed to you know

write this

so today I'm trying to show you that we

are

categorizing subcategory

into

four parts

okay so how can we do now I'm writing

we can do it using multiple methods I am

using case statement because we are

learning about case so case of sub

category and I'm calling when it is uh

accessories so one important thing that

we need to understand about case

statement is whenever you are calling

this subcategory

Tableau will try to read all the values

that are there in that subcategory item

and it follows and it is case sensitive

okay

so I am just trying to pass whatever is

there in this when accessories then I

want to call it as like say group one

and when it is uh

appliances

so I'm just taking uh

alternate values here

at

then I'm calling it as group

to

when

binders

then it is group two

when it is uh

chairs

then I'm calling it as

group one so randomly I'm I'm passing it

okay so you can try any other method

that you feel again the purpose is to

show you how we can write okay and uh

when so anything okay so when it is one

more we need when it is a papers

and it is paper then it is

group

3

when it is uh

phone

then it is group three

any other than this

any other value that is coming apart

from this then it is group

4

me and it simply

okay now if I click on apply

and if I take it

onto column shelf see how my groups are

getting categorized so if I don't want

this I can simply take my remove this

and if I take subcategory into label

now it is clearly showing how many items

or how many subcategory items we have in

groups so if I aggregate this

see

so maybe account distinct we have

Group 1 has two subcategory items group

two has two group three has one and

group four has got

12 subcategory items this way also we

can

you know use this to create headers

so you know many times we create headers

on top of something so this kind of a

requirement also you can get it in real

time

okay now what I'm doing is uh just to

show you one more use case here let us

play with some a number here

and I'm trying to

okay so let us take sub category here

and I'm taking profit here

okay so we got profit okay now what I'm

doing is I'm taking

uh I'm writing a simple calculation here

and I'm trying to write

profit by

sales I am doing

okay so not profit so sum of

profit

by

Marcel

so some I'm just trying to divide it so

that I can see the value and I'm writing

here as uh

logical 4 here

and I'm just writing it or dragging it

here

I'll make it discrete

now it is giving us some value right

again the whole point here is just to

show you one use case here now instead

of this

I am trying to write 0 here

division by zero

so now we are getting null value

so now observe here even though our

calculation is valid here okay it is

giving us zero now I want to handle this

okay one of the very very important you

know requirement in real time how can we

handle this if

um

okay so I want to just uh

call it as uh

okay so replace some of

sales

with zero

let us see what is the okay so first let

us comment this

still we are getting replace is called

with the float do you mean string stick

oh

okay so see now we are getting string

error here so

if I try to convert it into string

okay

uh I'm trying to just hard code my sum

of sales to zero

directly zero so that I can

fix it

[Music]

okay so what I'm trying to do is I'm

simply trying to zero all my sum of

sales so see here how I'm doing if my

sum of sales

is not equal to 0 then I want to print

zero okay else also 0 and it okay I'm

just trying to test this

and you see I'm getting 0 here

now I'm just trying to divide this

with our sum of profit

divided by this

okay now

now what is happening see here

everything we are getting null now I

want to handle my denominator that when

we are getting this 0 or when our

denominator is basically zero I want to

display 0 that's it okay otherwise it

should display profit that is the

requirement here how can we do

is

uh

I'm taking this statement here

so whatever in the is there in the

denominator I'm taking that

is equal to

zero

then

print as zero okay

else

it would be sum of

profit farming

that's it

end it

okay so now something okay so we will

comment our these two parts and we'll

try to test it

yeah so now what will happen it will

test my denominator part so don't get

confused this is my denominator part if

this is somehow 0

then directly print the calculation as 0

because anything divided by 0 is

actually not calculatable so directly I

am creating that as 0 otherwise print my

sum of profit that is how I am trying to

do here click on apply now see here

we're doing it like this

okay so we can play it play with our

logics

like the way we want we just need to

understand how we can build that

so now if I try to write this here

and I'm just showing you another use

case here

so this is valid now if I click on this

I'm getting uh

null value now I want to handle this

whenever we are getting null I want to

replace it with 0. simply you can use

another function called as 0 or sorry ZN

function and you can simply cover this

up

now what will happen in all the cases

wherever you are getting null Tableau

will directly automatically replace it

with 0. click on apply

now you are getting zero one of the very

important function which is used to

standardize your code so

one example

if I take on this and if I try to

compute

quick table calculation like say percent

of total I am trying to do

now when I double click this it will

edit and if I copy

and paste it here

uh

see the code it is trying to do uh okay

now what I'm trying to do is uh

maybe I want to percentage difference I

am trying to do

yeah and control this thing

now see here the way calculation is

being generated by Tableau it is also

using ZN function to standardize the

code

see here apps function it is trying to

use now what it is doing

denominator cannot be

you know divided by negative number so

what it is doing it is using absolute

function to standardize the code

in denominator we don't need negative

value so that's why whatever is coming

in the denominator we are just removing

the negative number from that or

negative symbol from that and making it

absolute value

that way we can standardize our code so

this is one very good example for us to

understand that how Tableau will write

the code so the same format is extracted

from mu in real time as well

okay so that's it from what I said in

this video I hope this video has helped

you if it does don't forget to like

share and subscribe see you in the next

video have a good day and bye