Tableau Tutorial: calculations

Showing posts with label calculations. Show all posts

Sign function in tableau

sign function will takes input as number if the input number is "+ve" it returns +1 ,

if the input number is "-ve" it returns -1 ,

if the input number is "0" it returns 0 ,

Syntax

sign(number)

Examples
sign(4)=1

Trimming functions in tableau

In tableau 3 types of trimming functions are available.

LTRIM

RTRIM

TRIM

LTRIM function removes the empty space from the left part of the input string.where as Rtrim removes the empty space from the right part of the string. where as Trim function eliminates empty spaces from both sides of the string.

syntax:

Ltrim(string)

example:

Ltrim(' tableau ') ='tableau '

syntax

Rtrim(string)

example

Rtrim(' tableau ') =' tableau'

syntax

Trim(string)

example:

trim(' tableau ') ='tableau'

Read more

Round function in tableau

Round function rounds the number of places in the input number and it displays the nearest value

syntax :

Round(number,[no-of-places)

while rounding the input number to 'n' places tableau will checks the 'n+1' values .If "N+1 value is '>=5'.in tableau will acts 1 to the n position value . if the number is less 5(<5) then it will display 'n' vale as it is.

Calculations in tableau

Normally each and every time in tableau it is not possible to obtain the meaningful output from the existing data field .some times we need to create the new field in the tableau known as the calculated field.this calculated fields can be obtain by writing calculations this calculations contains data fields ,parameters,functions and optionally comments also as this calculated field are deriving from the existing data fields this calculated fields are also known as as derived fields.

note
one calculated field can be used inside the second calculated field without restrictions.

Date functions in tableau

date add function

date add function takes the input as date and to input date it will add the interval based on the date part.

syntax:

DATEADD(datepart,interval,input-date)
where data part indicates month,year ,quarter ,day etc.

example

Date add('month'3,,#8-nov-2014#)=8-feb-2015

note
in tableau the date should be enclosed between the open# and the closed #

in date functions always the datepart should be specified in the lowercase.

DATE DIFF

date diff function finds the diff between two dates based on the datepart and optionally it will takes the 'week start day also'.

syntax
DATEDIFF(datepart,start-date,end-date,[weeksstartday])

DATE NAME:

the date name function takes the input as date along with the date part and it names that date part.

syn

datename('datepart',input-date,[week-start-day])

example datename('month',#8-nov-2014#)=nov

DATE TRUNK

date trunk function reduce the accuracy of the input date based on the date part.

syn
datetrunc (datepart,date,[week-start-day])

datetrunc('month',#8-nov-2014#)=1-nov-2014

DATE PARSE

The date parse function converts any string to the date based on the input format this function is available

from tableau 8.2 based on the specified format tableau will convert the data.

syntax:

Dateparse(dformat,string)

example

Date parse("dd.mm.yyyy,'08.10.2014')= 8-nov-2014

today,now functions

today function gives the current date where as now function gives the current date+time.

Table calculations

table calculations are the calculations that are applied to the entire table in tableau while performing
the table calculations we need to know two things.

what type of calculations we are performing

What type of calculation indicates the diff table calculations like diff from percent diff from ,
percent of total extra.

where we are performing the calculations

Where we are performing the calculations is having two parts

1.addressing part

2.partitioning part

addressing options:

the diff addressing options are available in tableau are

Table across:

If u performing the calculations across the table horizontally then
such a kind of addressing is known as table across.

Table down
If you are performing the calculations down the table vertically
then such a kind of addressing is known as table down.

Table across then down
If you are performing the calculations across the table horizontally the
down the table vertically then such a kind of addressing is known as
table across then down.

pane across
If you are performing the calculations across the pane horizontally then such
a kind of addressing is known as pane across.

pane down

If you are performing the calculations down the pane vertically then such a kind
of addressing is known pane down.

pane across then down
I f you are performing the calculations across the pane horizontally then down
the pane vertically then such a kind of addressing is known as pane across then down

partitioning part

Difference from

difference from is mainly used to display the growth in between two values:

Percent diff from
percent difference from is the second way to find out the growth in tableau. percent diff from is the best way to find out raising growth of any business as it gives the growth in terms of the percentage.

Percent from
percent from is mainly used to find out the absolute change in the tableau the percent from calculation is similar like the percent different from it displays the percentage of all the values by comparing with the required value.

Percent of total

the percent of total calculation .this calculation is very important if you want to find out each individual value control on the whole.

Types of Window Functions

In tableau if you want to perform the aggregations on the required values then we need to take all the required values as '1' window and we need to perform the aggregations.

the different window functions are available are:--

Window-Sum

Window_Avg

Window_Min

Window_Max

Window_Count

Syntax

Window_Sum(expression,[start],[end])

Diff between Count and Count distinct functions in tableau

Count function will count all the values in the input expression even though if the values are repeating where as count distinct function will count only the distinct values both count and count distinct will ignores the null values.

Syntax

Count(expression)

Countd(expression)

Example:

Product Sales Count(sales) countd(sales)

A 100

B 100 4 3

C 200

D 300

E

ZN function in tableau

ZN function is also called as zero number function.The syntax of the ZN function is ZN(number).
If the number is null or empty zn function converts that null value to zero.If the number is not null then tableau returns the same number

Aggregation in Tableau

ATTR is also known as attribute function. ATTR function is mainly used to aggregate the non aggregate values in tableau.in tableau if u want mix aggregate values in Tableau. In tableau if u want to mix aggregate to non aggregates we have to make use of this ATTR function.

Syntax: ATTR(expression)

logic:

IF max(expression)=min(expression) then expression

else '*" end

Rules:-

1.We can't place one aggregation inside the other aggregation.

2,We can't mix aggregated fields with the non aggregated field.

3.Max(sum(profit),sales)-------->invalid

4.Max(sum(profit),sum(sales))-------->valid

5.Sum(sales)*profits--------> invalid

6.Sum(sales)*7---------->valid