![Date Functions
• DATEADD (date_part, increment, date)
• DATEADD ('month', 3, #2004-04-15#) = 2004-0715 12:00:00 AM
• DATENAME (date_part, date, [start_of_week])
• DATENAME('month', #200404-15#) = "April“
• DAY (date)
• DAY(#2004-04-12#) = 12
• NOW( )
• NOW( ) = 2004-04-15 1:08:21 PM](https://image.slidesharecdn.com/tableaucalculationsexp3-250121142601-3643116e/85/Tableau-calculations-and-experience-pptx-8-320.jpg)
![Logical Functions
• These functions evaluate some single value or the result of an
expression and produce a boolean output.
• IFNULL (expression1, expression2)
• IFNULL([Sales], 0) = [Sales]
• ISDATE (string)
• ISDATE("11/05/98") returns true or false
• MIN(expression) returns min of expression.](https://image.slidesharecdn.com/tableaucalculationsexp3-250121142601-3643116e/85/Tableau-calculations-and-experience-pptx-9-320.jpg)
![Calculations
• Numeric calculations in Tableau are done using a wide range of
inbuilt functions available in the formula editor.
• We will see how to apply calculations to the fields. The
calculations can be as simple as subtracting the values of two
fields or applying an aggregate function to a single field.
• Create a profit_n_discount field by fields [profit]-[discount]
• Shipmode in columns profit_n_discount in rows.
• Avg_Sales calc field Avg[Sales] in rows.](https://image.slidesharecdn.com/tableaucalculationsexp3-250121142601-3643116e/85/Tableau-calculations-and-experience-pptx-11-320.jpg)
![String calculations
• Sales in column and City in rows.
• City_o calc field
• Formula CONTAINS([City],"o")
• Drop City_o in filters and make it true.](https://image.slidesharecdn.com/tableaucalculationsexp3-250121142601-3643116e/85/Tableau-calculations-and-experience-pptx-12-320.jpg)
![Date Calculations
• Create date_diff calc field
• Formula DATEDIFF('month',#2009-
03-21#,[Ship Date])
• Drop date_diff in dimensions
• Month(Ship Date) and date_diff in
rows
• Showme to horizontal bars](https://image.slidesharecdn.com/tableaucalculationsexp3-250121142601-3643116e/85/Tableau-calculations-and-experience-pptx-13-320.jpg)
![Fixed Level of Detail
• Find the sales in each region
• Sales in column, region and state in rows.
• Q> Find the amount of Sales for each state in each region?
• First create the formula field named Regional Sales using the formula.
• {FIXED [Region] : SUM ([Sales])}
• Drag the Region and State field to the Rows shelf and the calculated
field to the Text shelf under the Marks card.](https://image.slidesharecdn.com/tableaucalculationsexp3-250121142601-3643116e/85/Tableau-calculations-and-experience-pptx-20-320.jpg)
![Include Level Of Detail
• Q> Calculate the sum of sales per
state for each sub-category of
products.
• Drag the Sub-Category field to the
Rows shelf.
• INCLUDE level of detail expressions
compute values using the specified
dimensions in addition to whatever
dimensions are in the view.
• { INCLUDE [State] : SUM(Sales) } in
columns](https://image.slidesharecdn.com/tableaucalculationsexp3-250121142601-3643116e/85/Tableau-calculations-and-experience-pptx-21-320.jpg)
![EXCLUDE Level of Detail
Expressions
• Exclude Region from Sales figure
calculated for every month
• { EXCLUDE [Region] : SUM([Sales])}
• EXCLUDE level of detail expressions
specify dimensions to exclude from
the view level of detail.](https://image.slidesharecdn.com/tableaucalculationsexp3-250121142601-3643116e/85/Tableau-calculations-and-experience-pptx-22-320.jpg)
![Scenarios to
use
Calculations
• The data you need for your analysis is missing
from your data source. You need cost and you
have sales and profit.
• Create a Cost field by [Sales] – [Profit] in
Columns
• You want to quickly categorize data.
• IF SUM([Profit]) > 0
THEN "Profitable"
ELSE "Nonprofitable"
END
• You want to transform values in your
visualization.
• Calculate the difference in profit from one year to
the other.
• Yeardate in column, Category & subcategory in
rows. Profit in color and label.](https://image.slidesharecdn.com/tableaucalculationsexp3-250121142601-3643116e/85/Tableau-calculations-and-experience-pptx-24-320.jpg)
Tableau calculations and experience.pptx
- 1. Tableau calculations & expressions Rohith Murthy Non sharable Industry Synergized
- 2. Tableau operators • Types of Operator • General Operators • Arithmetic Operators • Relational Operators • Logical Operators
- 3. Operators • + ( adds two numbers or concatenates two strings ). • - ( Subtracts two numbers or dates ). • * ( Numeric multiplication ). • / ( Numeric division ). • % ( Reminder of numeric division ). • ^ ( Raised to the power ).
- 4. Operators • = or == ( equal ). • != or <> ( not equal to ). • > ( greater ) • < ( less than ) • Logical ( AND, OR, NOT )
- 5. Tableau Functions • Any data analysis involves a lot of calculations. In Tableau, the calculation editor is used to apply calculations to the fields being analyzed. Tableau has a number of inbuilt functions which help in creating expressions for complex calculations. • Following are the description of different categories of functions. • Number Functions • String Functions • Date Functions • Logical Functions • Aggregate Functions
- 6. Number Functions • CEILING(2.145) = 3 • POWER(5,3) = 125 • ROUND(3.14152,2) = 3.14
- 7. String Functions • LEN("Tableau") = 7 • LTRIM(" Tableau ") = "Tableau“ ( trims space ). • REPLACE("GreenBlueGreen", "Blue", "Red") = "GreenRedGreen“ • UPPER("Tableau") = "TABLEAU"
- 8. Date Functions • DATEADD (date_part, increment, date) • DATEADD ('month', 3, #2004-04-15#) = 2004-0715 12:00:00 AM • DATENAME (date_part, date, [start_of_week]) • DATENAME('month', #200404-15#) = "April“ • DAY (date) • DAY(#2004-04-12#) = 12 • NOW( ) • NOW( ) = 2004-04-15 1:08:21 PM
- 9. Logical Functions • These functions evaluate some single value or the result of an expression and produce a boolean output. • IFNULL (expression1, expression2) • IFNULL([Sales], 0) = [Sales] • ISDATE (string) • ISDATE("11/05/98") returns true or false • MIN(expression) returns min of expression.
- 10. Aggregate Functions • AVG(expression) – numerical fields only. • COUNT (expression) – returns number of items • Median (expression) – returns median of expression • STDEV (expression) - Returns the statistical standard deviation of all values in the given expression based on a sample of the population.
- 11. Calculations • Numeric calculations in Tableau are done using a wide range of inbuilt functions available in the formula editor. • We will see how to apply calculations to the fields. The calculations can be as simple as subtracting the values of two fields or applying an aggregate function to a single field. • Create a profit_n_discount field by fields [profit]-[discount] • Shipmode in columns profit_n_discount in rows. • Avg_Sales calc field Avg[Sales] in rows.
- 12. String calculations • Sales in column and City in rows. • City_o calc field • Formula CONTAINS([City],"o") • Drop City_o in filters and make it true.
- 13. Date Calculations • Create date_diff calc field • Formula DATEDIFF('month',#2009- 03-21#,[Ship Date]) • Drop date_diff in dimensions • Month(Ship Date) and date_diff in rows • Showme to horizontal bars
- 14. Quick table calculations • Select the measure on which the table calculation has to be applied and drag it to column shelf • Right-click the measure and choose the option Quick Table Calculation ….
- 15. Quick Table calculations • Quick table calculations are transformations you can apply to the values in a visualization. • You can use table calculations for a variety of purposes, including: • Transforming values to rankings. • Transforming values to show running totals. • Transforming values to show percent of total.
- 16. Quick table calculations • Choose one of the following options to be applied on the measure. • Running Total • Percent Difference • Percent of Total • Rank • Percentile • Moving Average • Year to Date (YTD) Total • Compound Growth Rate • Year over Year Growth • Year to Date (YTD) Growth
- 17. Quick table calculations • Table calculations allow you to transform values at the level of detail of the visualization only. • Setup Visualization • Drag Order Date to the Columns shelf. • Drag State to the Rows shelf. • Drag Sales to Text on the Marks Card • Drag Profit to Color on the Marks Card. • On the Marks card, click the Mark Type drop-down and select Square. • Right click on profit and perform quick table calculations.
- 18. Tableau Level of Detail ( LOD ) • Used to run complex queries. • There are three main types of LOD expressions. • FIXED LOD This expression computes values using the specified dimensions without reference to any other dimensions in the view. • INCLUDE LOD This level of detail expressions compute values using the specified dimensions in addition to whatever dimensions are in the view. • EXCLUDE LOD These levels of detail expressions subtract dimensions from the view level of detail.
- 19. Level Of Detail • They can be performed at a • more granular level (INCLUDE), • a less granular level (EXCLUDE), • or an entirely independent level (FIXED).
- 20. Fixed Level of Detail • Find the sales in each region • Sales in column, region and state in rows. • Q> Find the amount of Sales for each state in each region? • First create the formula field named Regional Sales using the formula. • {FIXED [Region] : SUM ([Sales])} • Drag the Region and State field to the Rows shelf and the calculated field to the Text shelf under the Marks card.
- 21. Include Level Of Detail • Q> Calculate the sum of sales per state for each sub-category of products. • Drag the Sub-Category field to the Rows shelf. • INCLUDE level of detail expressions compute values using the specified dimensions in addition to whatever dimensions are in the view. • { INCLUDE [State] : SUM(Sales) } in columns
- 22. EXCLUDE Level of Detail Expressions • Exclude Region from Sales figure calculated for every month • { EXCLUDE [Region] : SUM([Sales])} • EXCLUDE level of detail expressions specify dimensions to exclude from the view level of detail.
- 23. When to use calculations • Some examples might include: • To segment data • To convert the data type of a field, such as converting a string to a date. • To aggregate data • To filter results • To calculate ratios
- 24. Scenarios to use Calculations • The data you need for your analysis is missing from your data source. You need cost and you have sales and profit. • Create a Cost field by [Sales] – [Profit] in Columns • You want to quickly categorize data. • IF SUM([Profit]) > 0 THEN "Profitable" ELSE "Nonprofitable" END • You want to transform values in your visualization. • Calculate the difference in profit from one year to the other. • Yeardate in column, Category & subcategory in rows. Profit in color and label.
- 25. Tableau interface and metadata Ends.. BE HAPPY AND THINK SERIOUS IN LIFE. THANK YOU .