Tableau Visualizations: Creating Calculations in Tableau

Conditional Calculations:

We create calculations If any calculation you want to use in the report but

which is not there in the database, then we create that custom calculation with right syntax at tableau level.

And we use the Calculations for customization purposes.

EX:

Sales less than 10 K is "Worst Sales",

>10 K and < 15 K is "Average" ,

>15 K and < 20 K is "Above Average" ,

>20 K and < 50 K is "Meet Target" ,

>50 K and < 75 K is "Excellent Sales."

SYNTAX:

1) If condition with 1 Possibility without Else part, we get nulls wherever condition does not match.

IF [Condition Matches] then [Matched Value] end

EX: If sum([Sales])>15000 then "Good" end

Description: In the above example, If sales are more than 15000 then those will be represented as "Good" (If condition matches then "Good" if Condition does not match then we get empty, i.e. null)

If Sales does not exceed 15000 then those values are represented by empty since we did not define the else part. Next example will explain how output will affect if we define the else part.

2) If condition with 2 Possibilities including Else part

Syntax:

IF [Condition Matches] then [Matched Value] else [Non Matched Value] end

EX: If sum([Sales])>15000 then "Good" else "Average" end

Description:

In the above example, If sales are more than 15000 then those will be represented as "Good" (If condition matches then "Good" if Condition does not match then empty, i.e. null)

If Sales does not exceed 15000 then those values are represented by "Average" since we defined the else part.

3) If then else - more than 2 Possibilities

if [Condition 1 Match] then [Value1]

elseif [Condition 2 Match] then [Value 2]

elseif [Condition 3 Match] then [Value 3]

elseif [Condition 4 Match] then [Value 4]

.....................

Else [Value N] end

EX:

If sum([Sales]) >=15000 then "Good"

elseif sum([Sales]) >=10000 and sum([Sales]) <15000 then "Average"

elseif sum([Sales]) >=5000 and sum([Sales]) <10000 then "Below Average"

else "Bad" end

=================================================

Case <EXP>

when <Matches 1> then [Value 1]

when <Matches 2> then [Value 2]

when <Matches 3> then [Value 3]

Else [Value 4] end

Case [Region]

when "Central" then "Territory - 1"

when "East" then "Territory - 1"

else "Territory - 2" end

EX:

1) I want to Apply Different colors to Regions as per Sales Value.

<10 K - Color 1

>=10 K And < 15 K then Color 2

>=15 K And < 25 K then Color 3

or Color 4

2) Difference between And , Or Operators in the Calculations.

Create few reports with BUZ Requirement.

3) I want to see 2013, 2014 data in 1 color, 2011, 2015 in another color.

(Don't use the Editing Color Legend option for applying colors. Use Calculations to define colors)

4) Categorize the City's into different groups by using your own business situation.

5) I want to apply Different shapes as per Sales Values.

Use Question -1 as a situation & define the shapes.

Note:

As of now Tableau does not have "IN" operator in the Calculations but there is an alternative approach that is "OR".

Case [Region]

when "Central" then "Territory - 1"

when "East" then "Territory - 1"

else "Territory - 2" end

If [Region] = "Central" or [Region] ="East" then "Territory - 1"

else "Territory - 2" end

Functions to create custom calculated fields

1) ASCII:

It returns first letter of String's ASCII

ASCII(“Tableau”) = 84

2) Char:

This function converts numerical numbers into characters using given input number then mapping to the ASCII values list.

EX:

char(65)= A

3) Contains:

By using this function, we can find whether any specific word (substring)is contained in another column (String). It gives output as BOOLEAN, so we can filter "True".

This example is checking whether customer names contain the letter "A" irrespective of the position of that "A".

EX:

Region = South

contains([Region], "st") = False

contains([Region], "k") = False

contains([Region], "th") = True

contains([Region], "SO") = False

contains('abcd', 'ab') = True

contains('abcd', 'xy') = False

4) ENDWITH:

It will check whether the substring is endswith or not in the main string.

It means it gives the Outputs as True/False

EX:ENDSWITH( [Region], 'st' )

Region = South

endwith([Region], "th") = True

endwith([Region], "k") = False

5) Find:

It will check the position of the substring in the main string.

It means it gives the Outputs as Numerical Numbers.

Region = South

Find ([Region], "th") = 3

Find ([Region], "k") = 0

6) Findnth:

This function verifies the string & gives specified substring position

for nth occurrence.

Findnth(“developer”,1,’e’)

Findnth(“developer”,’e’,1) = 2

Findnth(“developer”,’e’,2) = 4

Findnth(“developer”,’pe’,2) = 7

7) Isdate:

This FUNCTION is used to test the given string data, if the given data is date then returns “True” else false.

EX: isdate(‘[Order_Date]’) = False/True

Isdate(’12-Feb-2011’) = True

Isdate(’29-Feb-2011’) = False # Since year 2011 is not a Leap Year

Isdate(’29-ABC-2011’) = False

Isdate(’2011-Dec’) = True

8) LEFT:

We use this function to fetch substring from starting point to

specific point in the given Main String.

EX: Left([Customer Name],3)

If Region = South then Left([Region],2) = So

Left([Region],4) = Sout

===================

9) LEN:

This function counts the number of characters including nulls & returns Numerical numbers.

=====

10) MAX:

Test & returns Maximum as output., It takes the first character of a given string then finds the ASCII value with another string’s first character then returns the maximum string as the output.

Max(“A”, “B”)= B

Max(“AAAA”, “D”)= D

Max(“ACA”, “aZ”)= ‘aZ’

================
11) Min:

This function checks it gives minimum number as output as per

first character’s ASCII value.

EX: Min ('A', 'B') = A

12) MID:

This function is used to extract the substring from specific point

to a specific point.

EX: Region = South

Mid( [Region], 2,4) = outh

MID(MAIN STRING, STARTING POSITION, NO.CHAR WANT)

13) Replace:

This function is used to replace the substring by another substring.

EX: replace([Region], “th”, “th Carolina”) = South Carolina

14) Right:

This function fetch the data up to specified number of positions

from the right side.

right('Tableau', 3) = 'eau'

15) RTRIM:

This function removes empty space after the word, which means the right side is empty space.

16) Space:

This function is used to add the space in the word &

defined number of times. we can add extra space either at the starting position of the string i.e. as prefix or at the end of the string i.e. as suffix.

EX:

space(2) +”ABCD” = “ ABCD”

”ABCD” + space(2) = “ABCD ”

17) Stars with:

This function tests whether string starts with specified substring &

returns Boolean If it is true.

EX:

startswith('Tableau', 'asd') = False

startswith('Tableau', 'Tab') = True

startswith('Tableau', 'T') = True

startswith('Tableau', 'able') = False

18) Trim:

This function removes extra space of string for left & right edges of the given string.

EX:

trim(‘ ASDF ‘) = ‘ASDF’

trim(‘ ASDF‘) = ‘ASDF’

trim(‘ASDF ‘) = ‘ASDF’

----------------------

19) Upper:

It converts the given string into Upper Case.

—-------------- —---------------- —------------------ —--------------------

In today’s session we will discuss Date Calculation,

How to create calculations using Date Dimension.

Tableau supports the following Date formats.

Tableau Supported the following parts of the Date.

DATE_PART VALUES

'year' Four-digit year

'quarter' 1-4

'month' 1-12 or "January", "February", and so on

'dayofyear' Day of the year; Jan 1 is 1, Feb 1 is 32, and so on

'day' 1-31

'weekday' 1-7 or "Sunday", "Monday", and so on

'week' 1-52

'hour' 0-23

'minute' 0-59

'second' 0-60

Dateadd:

We use this function to perform Date Calculations

Like addition or subtraction at different levels.

Levels: Year, Quarter, Month,.........

EX: dateadd('month', 2, #12-Jan-2014#) ---- Adding 2 months to the date '12-Jan-2014'

O/P: 12-Mar-2014

This View: Scenario:

Imagine we take 2 months of time to deliver once a order is placed.

So I want to see Delivery Date for Each Order.

Process:

1) Right click on [Order Date], click on "Create Calculated Field"

2) Define the Name Syntax as below.

Name: Deliver Date

Syntax: dateadd('month', 2, [Order Date])

3) Then add the [Order Date], [Deliver Date] to report.

4) Now the report will show the Data at Year level. If you want to see in terms of Full date, Right click on [Order Date], click on "More" option then click on "Custom" then select "Month/Day/Year" format from the Drop down List.

*************

Datediff:

We use this function to find the difference between two DATES in terms of specified date parts like Year, Month, Date and it gives the O/P in integers.

Once created the Calculated field, drag that into the Dimension window.

EX:

syntax: DATEDIFF('day',[Order Date],[Ship Date] )

This View: Scenario:

I want to see How many days does take to deliver each Order ( Using Sample Database)

1) Right click on [Order Date], click on "Create Calculated Field".

2) Define the Name Syntax as below.

Name: Days taken for Deliver

Syntax: DATEDIFF('day',[Order Date],[Ship Date] )

3) Then add the [Order Date], [Days taken for Deliver] to report.

4) Now you can find the [Order Date], [Days taken for Deliver] on the report . Days taken for Deliver shows Numerical Number.

***************************

Datename

We use this function to extract specific parts of the Date, It gives O/P as string.

EX:

1) datename('year', #12-Jan-2014#)

O/P: 2014

2) datename('month', #12-Jan-2014#)

O/P: Jan

3) datename('weekday', #12-Jan-2014#)

O/P: Sunday

**************

Datepart

We use this function to extract specific part of the Date,

It gives O/P as Numerical Data.

EX:

1) datepart('year', #12-Jan-2014#)

O/P: 2014

2) datepart('month', #12-Jan-2014#)

O/P: 1

3) datepart('weekday', #12-Jan-2014#)

O/P: 1

****

Converts a string to a datetime in the specified format.

EX:

DATEPARSE ("dd.MMMM.yyyy", "15.April.2014") = #April 15, 2014#

DATEPARSE ("h'h' m'm' s's'", "10h 5m 3s") = #10:05:03#

***********

Datetrunc

We use this function to get the First Date of a given Date at a Defined Level. (Opening Date)

EX:

1) Datetrunc('year', #12- Feb-2014#)

O/P: 1-Jan-2014

2) Datetrunc('month', #12- Feb-2014#)

O/P: 1-Feb-2014

Week starts with SUNDAY.

This View: Scenario:

I want to see what is the Day of the Year for Each Transaction (#Days in a year are 365 or 364)

1) Create a Calculated field like below to get the First Day of the Year for Each Order.

Name: Datetrunc - Year

Syntax: DATETRUNC('year',[Order Date])

2) Create another Calculated Field

Name: Day of the Year

Syntax: datediff('day', [Datetrunc - Year],[Order Date])

3) Drag the Fields [Order Date], [Day of the Year], [Datetrunc - Year] onto the Work Area.

***************

Day, Month, Year

-> We use these functions to extract the Specific part from the Date. It means Year, Day, Month. And it gives Integer as Output.

-> Once you create a calculated field using any of these functions, that will be moved into the Measures window since this function gives O/P in Number Data type.

EX:

1) day(#12-Jan-2014#)

O/P: 12

2) month(#12-Jan-2014#)

O/P:1

3) year(#12-Jan-2014#)

O/P:2014

5) Now you can find the [Order Date], [Deliver Date] on the report with "Month/Day/Year" format.

-------------------------------------------------- ------------------------------ -----------------------------

Handling Null Values

We are using the following dataset to work on the nulls data.

String Data:

We use isnull([String_Column]) to verify whether the String_Column is having null Records, output of this function is boolean we can use it in the calculations.