**************************Day-1*******************************
Overview about Tableau Software
Before we begin about tableau, we will see what is need of data in Business is.
Business Decision Makers need effective views of data to understand results, discover relationships, find patterns, locate outliers, uncover structure, and summarize findings, Insights of Business. How well can we see what is going on in our business?
Tableau Software provides software applications for fast analytical and rapid fire business intelligence. We can make answer virtually any business question by dragging-and-dropping your data into a free-form visual canvas. We can create beautiful graphs, reports and dashboards, then share those results in just a few clicks. Using Tableau Desktop to build and interact with views of data. These views allow us to query, display, analyze, filter, sort, group, drill down, drill up, calculate, organize, summarize, and present data faster and more efficiently than ever before. With Tableau Sever and Tableau Public you can share and embed our live, interactive views, reports, and dashboards so that colleagues can interact, customize or monitor them.
Components of Tableau Software
1) Tableau Desktop
2) Tableau Server
3) Tableau Reader
==Tableau Desktop
Tableau Desktop is a data visualization application that lets you analyse virtually any type of structured data and produce highly interactive, beautiful graphs, dashboards, and reports in just minutes. After a quick installation, you can connect to virtually any data source from spreadsheets to data warehouses.
Tableau Server
Tableau Server is a business intelligence solution that provides browser-based visual analytics anyone can use at just a fraction of the cost of typical BI software. With just a few clicks, you can publish or embed live, interactive graphs, dashboards and reports with current data automatically customized to the needs of everyone across your organization. It deploys in minutes and users can produce thousands of reports without the need of IT services — all within your IT infrastructure.
Tableau Reader
Tableau Reader is a free viewing application that lets anyone read and interact with packaged workbooks created by Tableau Desktop.
==Users:
Small, Medium & Large size companies are using Tableau as visualisation & Reporting tool.
Customers include GE, Novartis, Bank of America, Google, Cleveland Clinic, GM, CNBC, Microsoft, Wells Fargo, the District of Columbia, Allstate, Cornell and Harvard.
Opening and Closing the Application
The first thing to understand is how to open and close the application.
==Open Tableau
There are many ways to open Tableau from your desktop computer. Open the application by doing one of the following:
• Double-click the Tableau icon on your desktop.
• Select Start > All Programs > Tableau.
• Double-click a Tableau workbook or bookmark file. Tableau files are typically stored in the My Tableau Repository folder of your My Documents folder.
• Drag a data source such as an Excel or Access file onto the Tableau icon or the application window. Tableau automatically makes a connection to the data source.
==Close Tableau
When you are done working in Tableau you should save your work and close the application. Close the application by doing one of the following:
• Click the Close icon located in the right corner of the application title bar.
==Workbooks and Sheets
Tableau uses a workbook and worksheet file structure, like Microsoft Excel Workbook & Excel sheet.
• Workbooks
• Sheets
==Workbooks
Tableau workbook files are much like Microsoft Excel workbooks. They contain one or more worksheets or dashboards and hold all of your work. They allow you to organize, save, and share your results.
When you open Tableau, a blank workbook is automatically created. You can also create a new workbook by selecting File > New or by pressing Ctrl + N on your keyboard. You can open an existing workbook by doing one of the following:
• Double-click the thumbnail image of the workbook on the start page. The start page shows workbooks that you’ve recently used.
• Select File > Open and navigate to the location of your workbook using the Open dialog box. Tableau workbooks have the .twb or .twbx file extensions.
• Double-click on any workbook file.
• Drag any workbook file onto the Tableau desktop icon or onto the running application.
The workbook name is displayed in Tableau’s title bar.
The Tableau Workspace
The Tableau workspace consists of menus, a toolbar, the Data window, cards that contain shelves and legends, and one or more sheets. Sheets can be worksheets or dashboards.
Worksheets contain shelves, which are where you drag data fields to build views. You can change the default layout of the shelves and cards to suit your needs, including resizing, moving, and hiding them.
Dashboards contain views, legends, and quick filters. When you first create a dashboard, the Dashboard is empty and all of the worksheets in the workbook are shown in the Dashboard window.
==Dimensions – Fields that typically hold discrete qualitative data. Examples of dimensions include dates, customer names, and customer segments.
Dimensions are key objects of Business which are used to describe the Business, Example of Dimensions are Product, Customer, Geography,………
==Measures – Fields that typically hold numerical data that can be aggregated. Examples of measures include sales, profit, and number of employees, temperature, frequency, and pressure.
Measure are key figure which are used to evaluate the Business. Which are used to analyse the business.
==Sets – An additional area that stores custom fields based on existing dimensions and criteria that you specify. Named sets from an MS Analysis Services server or from a Teradata OLAP connector also appear in Tableau in this area of the Data window. You can interact with these named sets in the same way you interact with other custom sets in Tableau.
==Parameters – An additional area that stores parameters that you have created.
Parameters are dynamic variables that can be used as placeholders in formulas.
==Cards and Shelves
Every worksheet contains a variety of different cards that you can show or hide. Cards are containers for shelves, legends, and other controls. For example, the Marks card contains the mark selector, the size slider, the mark transparency control, and the shape, text, color, size, angle, and level of detail shelves.
Cards can be shown and hidden as well as rearranged around the worksheet.
The following list describes each card and its contents.
• Columns Shelf – we use Columns Shelf to drag fields to add columns to present the view.
• Rows Shelf - we use Columns Shelf to drag fields to add rows to present the view.
• Pages Shelf– We use Pages shelf where we can create several different pages with respect to the members in a dimension or the values in a measure.
• Filters Shelf– It contains the Filters, use this shelf to specify the values to include in the view.
• Measure Values Shelf – contains the Measure Values shelf; use this shelf to use multiple measures along a single axis. This shelf is only available when there is a blended axis in the view.
• Color Legend – contains the legend for the color encodings in the view and is only available when there is a field on the Color shelf.
• Shape Legend – contains the legend for the shape encodings in the view and is only available when there is a field on the Shape shelf.
• Size Legend – contains the legend for the size encodings in the view and is only available when there is a field on the Size shelf.
• Map Legend - contains the legend for the symbols and patterns on a map. The map legend is not available for all map providers.
• Quick Filters – a separate quick filter card is available for every field in the view. Use these cards to easily include and exclude values from the view without having to open the Filter dialog box.
• Parameters – a separate parameter card is available for every parameter in the workbook. Use these cards to modify parameter values.
• Marks – contains a mark selector where you can specify the mark type as well as the Path, Shape, Text, Color, Size, Angle, and Level of Detail shelves. The availability of these shelves are dependent on the fields in the view.
• Title – contains the title for the view. Double-click this card to modify the title.
• Caption – contains a caption that describes the view. Double-click this card to modify the caption.
• Summary – contains summary of each of the measures in the view including the Min, Max, Sum, and Average.
• Map Options - allows you to modify the various labels and boundaries shown in the online maps. Also you can use this card to overlay metro statistical area information.
• Current Page – contains the playback controls for the Pages shelf and indicates the current page that is displayed. This card is only available when there is a field on the Pages shelf.
Each card has a menu that contains common controls that apply to the contents of the card. For example you can use the card menu to show and hide the card. Access the card menu by clicking on the arrow in the upper right corner of the card.
===Files and Folders
You can save your work using several different Tableau specific file types: workbooks, bookmarks, packaged data files, data extracts, and data connection files. Each of these file types are described below.
• Workbooks (.twb) – Tableau workbook files have the .twb file extension and are marked with the workbook icon. Workbooks hold one or more worksheets and dashboards.
• Bookmarks(.tbm) – Tableau bookmark files have the .tbm file extension and are marked with the bookmark icon. Bookmarks contain a single worksheet and are an easy way to quickly share your work.
• Packaged Workbooks (.twbx) – Tableau packaged workbooks have the .twbx file extension and are marked with the packaged workbook icon. Packaged workbooks contain a workbook along with any supporting local file data sources and background images. This format is the best way to package your work for sharing with others who don’t have access to the data.
• Data Extract Files (.tde) – Tableau data extract files have the .tde file extension and are marked with the extract icon. Extract files are a local copy of a subset or entire data source that you can use to share data, work offline, and improve database performance.
• Data Connection Files(.tds) – Tableau data connection files have the .tds file extension and are marked with the data connection icon. Data connection files are shortcuts for quickly connecting to data sources that you use often.
**************************Day-2*******************************
We can build different kind of List, Crosstab, Graphs and Maps Reports using Tableau Software.
Our data visualisation tool of choice is Tableau, because it connects to pretty much any type of data, offers a very wide range of visualisation types and can handle huge volumes of data remarkably quickly when used well. But up until now we have always treated Tableau as a standalone tool sitting alongside whichever performance testing or metrics collections tools we are using on a performance assignment. That works fine – but it does mean that the analysis and visualisation doesn’t form an integral part of our workflow in these other tools. There are lots of opportunities to streamline the workflow, allowing interactive exploration of test results data – drilling down from high-level summaries showing the impact to low-level detail giving strong clues about the cause of issues. If only we could carry context from the performance testing tool to the visualisation tool.
===1) Simple List Report – Single Field
1) Open Tableau Software & connect to the required Database. ( Coffee Chain (MS Access) has been taken as data source in this Example)
2) Select Product field from Dimension shelf & hold Control & Select Sales from Measures shelf.
3) Click on Show Me, select “Text Tables”
===2) Simple List Report – Multiple Fields
1) Open Tableau Software & connect to the required Database. ( Coffee Chain (MS Access) has been taken as data source in this Example)
2) Select Product, State fields from Dimension shelf & hold Control & Select Sales, Profit fields from Measures shelf.
3) Click on Show Me, select “Text Tables”
===3) Simple List Report – Combination of Rows & Columns
1) Open Tableau Software & connect to the required Database. ( Coffee Chain (MS Access) has been taken as data source in this Example)
2) Drag Market Field from Dimension shelf to Column shelf Drag Date Field from Dimension shelf to Row shelf. And drag Sales field from Measures shelf to intersection of rows & columns in work area.
3) Click on “+” symbol of Date field on Row shelf then we get the Quarters, & click on Quarters “+” symbol then we get the Months data.
4) Apply the Formatting
====1) Simple List Report – Combination of Rows & Columns
1) Open Tableau Software & connect to the required Database. ( Coffee Chain (MS Access) has been taken as data source in this Example)
2) Drag Market Field from Dimension shelf to Column shelf Drag Date Field from Dimension shelf to Row shelf. And drag Sales field from Measures shelf to intersection of rows & columns in work area.
3) Click on “+” symbol of Date field on Row shelf then we get the Quarters, & click on Quarters “+” symbol then we get the Months data.
Apply the Formatting
=====5) Applying filter – Measures
1) Open Tableau Software & connect to the required Database. ( Coffee Chain (MS Access) has been taken as data source in this Example)
2) Drag Market Field from Dimension shelf to Column shelf Drag Date Field from Dimension shelf to Row shelf. And drag Sales field from Measures shelf to intersection of rows & columns in work area.
3) Click on “+” symbol of Date field on Row shelf then we get the Quarters, & click on Quarters “+” symbol then we get the Months data.
4) Drag Sales field from Measures shelf to Filters shelf and select “Sum”, define the range which you want to show on the report. In this example we are defining ranges Above 5000.
=======6) Applying colour formatting
1) Open Tableau Software & connect to the required Database. ( Coffee Chain (MS Access) has been taken as data source in this Example).
2) Drag Market Field from Dimension shelf to Column shelf Drag Date Field from Dimension shelf to Row shelf. And drag Sales field from Measures shelf to intersection of rows & columns in work area.
3) Click on “+” symbol of Date field on Row shelf then we get the Quarters, & click on Quarters “+” symbol then we get the Months data.
4) Drag Market Field from Dimension shelf to Colour Marks shelf, it assign the colours automatically.
5) If we want to change the colours, double click on “South” on colours mark then assign the required colours.
6) The following example is illustrating colour formatting is applied based on Quarters.
**************************Day-3*******************************
1) Initially we need to connect to database to analyse the business.
2) It is crucial phase since we need to fetch the data & defining relations among the tables. If we connect to multiple data bases, we need to define proper relationships between data bases.
3) Tableau supports different kind of data sources including Relational, Multi-Dimensional, MS-Excel and Text files.
Tableau Data Extract File
Microsoft Access Database
Microsoft Excel Workbook
Text File
Tableau Server
Aster Data nCluster
Cloudera Hadoop Hive
Firebird Database
Greenplum Database
IBM DB2 Database
InterSystems Caché Database
Microsoft Analysis Services Database
Microsoft PowerPivot
Microsoft SQL Server Database
MySQL Database
Netezza Database
OData
Oracle Database
Oracle Essbase Database
ParAccel Database
PostgreSQL Database
Progress OpenEdge Database
Sybase IQ Database
Teradata Database
===Connect to a Data Source
1) Click on Microsoft Excel,
2) Provide path where file is located.
3) Drag required tables in to right panel like the below figure. ( In this Example Sheet 1 is selected for reporting)
====Connecting to secondary Data source.
1) Right now Tableau connected to XYZA file, now we connect to secondary data source in this section.
2) Click on “Connect to Data” icon on tool bar.
3) Define the path of second data source file path. In this Example we have taken “ABC” as second data source.
4) Drag required tables in to right panel like the below figure. ( In this Example Sheet 1 is selected for reporting)
5) Click on goto worksheet.
6) Click on data menu bar, click on “Edit Relationships”, Define relationship between to data sources.
7) Now we can use two data sources for reporting.
Note: Here Primary Data Sources are highlighted with Blue colour, Secondary with Orange.
====Defining relationships among the tables and applying filter.
a. Open the Tableau Software by double click on Tableau Software Icon.
b. Connect to the Data Base,
c. Drag required tables in to right panel like the below figure. In this Example Fact Table, Product, Location tables are selected for reporting.
d. Tableau Engine defines the Relationships automatically. Although we can also define our own relationships.
e. And we can apply the filter before we connect to the work area.
Click on Filters which is located on top right side
f. Click on add, in the following example We are applying filter on field “ Market” & select “Central” & “West”.
Central” & “West” markets only available to the report since we applied Data source filters.
g. After apply the filter, click on go to work sheet.
h. Drag Market, Product fields onto row shelf, sales onto column shelf.
Modifying Data source filters:
1) Right click on Data source.
2) Now we can modify filter here as we want. In this example we are adding one more filter on “Product Line” column.
=========================================Day-4, 5,6============
Generally we use the filters to reduce the number of records present on the report or to show only required data on the report & to improve the performance.
Tableau have the following different kind of filters
• Regular Filters
? Regular Filters – Dimensions
? Regular Filters – Measures
? Regular Filters – Date
• Quick Filters
• Context Filters
=====Regular Filters - Dimensions:
• All fields that are filtered show on the Filters shelf.
• We can apply the Regular flirters either by dragging fields onto Filers shelf or in the view right click then select either hide or exclude.
• Right click on Field from the row shelf.
=====Here we can filter the data in 4 different ways
• General
• Wildcard
• Condition
• Top
The below picture is having the data without applying any filters.
a. General: select the required data which should only be showed on the report.( Ex: California, Florida, Texas, which is showed in the below picture)
b. Wildcard: We can filter the data by applying Wildcard. ( Ex: Sates names starts with “N”, which is showed in the below picture)
c. Condition: We can filter the data by applying condition. ( Ex: States which sales more than 25000, which is showed in the below picture)
d. Top: We can filter the data by define top n records. ( EX: Top 6 Cities sales wise, which is showed in the below picture)
=====Regular Filters - Measures: (Quantitative Filters)
We can apply filter on measure fields by applying aggregation filters.
In the following example, we are applying filter on Sales which should more than or equal to 5000.
Before apply the Filter, Tableau Engine automatically defines Min & Max values
======Date Filters:
We use this kind of filters to filter the Date Data type kind of Data. By using this kind of filters we can show Required Years, or Months or Quarters Data, ( Jan-2015 or Yr-2015, Q1-2013.......)
Or We can show data for Specific range. (EX: From 1-Jan-2011 to 12-Sep-2013,.........)
EX 1: List the customers who made Transactions in Year - 2011
1) Drag [Region], [Order Date], [Customer Name] fields into Rows shelf.
2) Drag the one of Measure fields from Measures window into Column Shelf.
3) Drag the [Order Date] into Filters shelf, then it will the list , here select Years , then check the box for Year -2011.
Tableau let's filter the Date Data in 2 Different ways.
1) Relative Date Filters.
2) Range Date Filters.
*********************
Relative Date Filters.
Relative date filters are dynamic, here values are changed as system date get changes.(Operating System)
EX: Relative - Current Year
Today Date #09-Jul-2015#
Description:
If you ask this Month Sales Data, It will show you Jul-2015 Data.
If you run the same report in Next month, it will show you Aug-2015 Data.
Means here we are filtering the Data like Current month, it will change as per the Day you run the Report.
Tableau is offering the following different kind of Relative Date Filters.
Years, Quarter, Months,........
EX: Today Date #09-Jul-2015#
Show me the Current Year Sales.
Today is #09-Jul-2015#, so it will show 2015 Data (including Future data also if database contains)
1) Drag [Region], [Order Date], [Customer Name] fields into Rows shelf.
2) Drag the one of Measure fields from Measures window into Column Shelf.
3) Drag the [Order Date] into Filters shelf, then it will the list that contains Relative & Range Filters, here select Relative then click on Years, Then Click on "This Year" radio button.
4) As of now this report shows 2015 Data.
5) If you run the same report after 1 year, it will show 2016 Data. ( DB should have 2016 data rather than it will show empty report).
EX: I want to show current Month, Quarter, Last Quarter this Year, Last month this Year, .........
I want to see last 3 Years of data on the Report.
*********************
Range Date Filters.
This kind of filters allow us to define the Static range dates.
And here we can't change date data, it means it is fixed Date filters.
EX: Today we have created a report for year - 2012, if you run the same report in net year, it will show YR-2011 data only, now we can say these filter values are fixed. ( Dont consider Parameter, Quick Filters in this scenario.)
EX:
1) Drag [Region], [Order Date], [Customer Name] fields into Rows shelf.
2) Drag the one of Measure fields from Measures window into Column Shelf.
3) Drag the [Order Date] into Filters shelf, then it will the list that contains Range Filters, here select Range then Define Starting & Ending Dates.
4) As of now this report shows 2015 Data.
5) If you run the same report after 1 year, it will show 2016 Data. ( DB should have 2016 data rather than it will show empty report).
*********************
Tableau offers filtering the data in different ways also, we can call these are static filters.
1) Year wise, Month wise, Quarter wise, Week wise,............
2) Starting Date to Till end of the Date as per data in the DB.
3) Up to certain Ending Date from starting Date.
*********************
Removing Nulls Data from the Report.
My data have nulls in Date Columns for Some Dates, i want to remove from the Report.
EX:
1) Drag [Region], [Order Date], [Customer Name] fields into Rows shelf.
2) Drag the one of Measure fields from Measures window into Column Shelf.
3) Drag the [Order Date] into Filters shelf, then it will show the list that contains Range Filters, here select Relative then click on "Special", then click on "Non-null dates" option.
4) Now Report don't have the null Dates.
==================================================================
EX:
1) I Want to see data for Current month only.
2) I want to see Sales Information from 1-Jan-2015 to Today.
3) I want to see data for Year 2011 & 13 & 15
4) What is the Orders Amount for Current Quarter?
5) I want to see This year March Month Data. ( with out using Range Filter, Use alternative approach)
6) I want to See Previous Year Data.
My data have nulls in Date Columns for Some Dates, i want to remove from the Report.
===================================================
Filters - Measures
We can filter the data on measures also by dragging the measure column & drop into filters shelf then click on "SUM" or any other Aggregate function then click o OK, then report will show filtered data.
And
We can filter the Measures range specific or filtering Nulls, or showing only nulls..........
****************
=============================================
Quick Filters:
-> We use the Quick Filters to include or exclude the members from the report.
-> It offers more interaction with report,It means End user can select required data instantly.
If user don't want to see the data on the report, then they can deselect that specific member from Quick Filter.
EX:
1) Drag [Region], [Order Date], [Customer Name] fields into Rows shelf.
2) Drag the one of Measure fields from Measures window into Column Shelf.
3) Right click on [Region] in the work area then click on Show "Quick Filter".
Changing the UI:
1) Single Value (List): This option offers select only 1 member at a time or select all members by selecting "All" radio button.
It does not allow selecting multiple members from the selection criteria.
2) Single Value Drop Down List:
This option also offers select only 1 member at a time or select all members by selecting "All" radio button.
It does not allow selecting multiple members from the selection criteria.
3) Single Value Slide Bar:
This option also offers select only 1 member at a time or select all members by selecting "All" radio button.
It does not allow selecting multiple members from the selection criteria.
4) Drop Down Multiple Values:
This option also offers select either at 1 or multiple members a time or select all members by selecting "All" check box.
5) Drop Down Multiple Values:
This option also offers select either at 1 or multiple members a time or select all members by selecting "All" check box.
Here List of the values shown in the drop down list.
6) Drop Down Multiple Values:
This option also offers select either at 1 or multiple members a time or select all members by selecting "All" check box.
Here List of the values shown in the drop down list.
7) Custom Value List:
This option also offers select either at 1 or multiple members a time or select all members by typing the characters.
It will search the members which are related to your typed word.
If type ST, it will show the data EAST, WEST then select which ever members want to show on the report.
It is quite similar to Contains filter.
*****************
Similarly we can apply the Quick filters for Measures.
===Quick Filters
Quick Filters are Prompts, which allows end user to select the required data, it increases user interactivity with reports.
Example:
1. Right click on Date field on data window, click on “Show Quick Filter”.
2. Now we get the Prompt, and we can select whichever we want to see on the report.
3. For an Example, Select 2011, 2014. Rest of the years will be filtered.
=====Context Filters
By default, all filters that you set in Tableau are computed independently. That is, each filter accesses all rows in your data source without regard to other filters. However, you can set one or more categorical filters as context filters for the view. You can think of a context filter as being an independent filter. Any other filters that you set are defined as dependent filters because they process only the data that passes through the context filter.
You may create a context filter to:
• Improve performance – If you set a lot of filters or have a large data source, the queries can be slow. You can set one or more context filters to improve performance.
• Create a dependent numerical or top N filter – You can set a context filter to include only the data of interest, and then set a numerical or a top N filter.
For example, suppose you’re in charge of breakfast products for a large grocery chain. Your task is to find the top 10 breakfast products by profitability for all stores. If the data source is very large, you can set a context filter to include only breakfast products. Then you can create a top 10 filter by profit as a dependent filter, which would process only the data that passes through the context filter.
Context filters are particularly useful for relational data sources because a temporary table is created. This table is automatically generated by Tableau when you set the context, and acts as a separate (smaller) data source that results in increased performance when you build data views.
Example of creating Context Filters:
• Drag Field onto Row shelf, Date onto Column shelf.
• Drag Date field from data window to filters, and click on years, Select 2012, 2014.
• Right click on date field in the filter, click on “Add to Context”.
Once we convert Normal filters as Context filters, Filters colour is changed from blue to black.
=======================Day- 7========================
Conditional Calculations:
We create calculations when If any calculation you want to use in the report
which is not there in database, then we create that calculation with right syntax at tableau level.
or
And we use the Calculations for customization purpose.
For An Example: 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
IF [Condition Matches] then [Matched Value] end
EX: If sum([Sales])>15000 then "Good" end
Description: In the above example, If sales are more tan 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 more then 15000 then those values are represented by empty since we did not define else part.
Next example will explains how out put will effect if we define 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 tan 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 more then 15000 then those values are represented by "Average" since we defined 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 "Below Average"
elseif sum([Sales]) >=15000 and sum([Sales]) <25000 then "Average"
elseif sum([Sales]) >=25000 and sum([Sales]) <50000 then "Above Average"
else "Excellent" 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
AAAAAA
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 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 apply Different shapes as per Sales Values.
Use Question -1 as situation & define the shapes.
Note:
As of now Tableau does not have "IN" operator in the Calculation but there is an alternative that is "OR"
Case [Region]
when "Central" then "Territory - 1"
when "East" then "Territory - 1"
else "Territory - 2" end
Or
If [Region] = "Central" or [Region] ="East" then "Territory - 1"
else "Territory - 2" end
====================Day - 8============
Hierarchies:
We use Hierarchies to organize the data
from Top Level information to Bottom level Information.
Top Level Information hold the Summary data, Bottom Level holds detail level data.
Sumup the botttom level data to get next level data.
Creating Hierarchies:Geographical
1) Before start creating Hierarchies, define no. of level &
Which level data holds in which column
2) The following are sequence of levels
Region -> State -> City
3) Right click on Region, click on "Create Hierarchy".
4) Define Name of the Hierarchy as "Location", then Add the Region to this Hierarchy.
5) Then right click on "State" then click on "add to Hierarchy" then click on "Location".
6) Add rest of the levels to hierarchy.
****************************************
Changing Default Properties:
-> We can change the Default properties like Color, Shape, Aggregations,
Comments, Aliases,Sort, Number Format.
EX:
1) Drag the [Region], [Sales] onto Work Area.
2) [Sales] takes the Sum as default aggregate function.
3) Right click on [Sales] in measures window, click on Default Properties,
click on "Aggregation", select "AVG"
4) Again drag the [Sales], it takes "AVG" as default aggregate.
****************************************
Defining Starting Month of Year(Defining Fiscal Year)
Generally Calendar Years starts with Jan Month,
Indian Fiscal year starts with Mar Month.
-> Right click on "database", click on Date Properties, click on "Fiscal Year Start"
then define starting month of the year.
****************************************
Adding external Images to the Report
1) Basically Images are stored at Documents\My Tableau Repository\Shapes
2) We can add external images at this area.
3) Create a folder, add images into this folder.
4) come to the report, Drag any Dimension into Work Area, Then select "Shapes" as the mark.
5) Then edit the Shapes , then then Reload the Shapes in the view.
6) Then Select the New folder which was created in previous steps.
7) Then you can assign the Shapes accordingly.
====================Day - 8============
Parameters – Parameters are dynamic variables that
can be used as placeholders in formulas.
Replacing the Static values by Dynamic Values.
EX: Dynamically selecting Columns (Fields), Dynamically
selecting Top 10,
Bottom N
EX: Dynamically Select Measure Field.
1) Right click in dimension window, click on Create Parameter.
2) Define the Type: String,
3) Select List option since we are defining manually.
Sales
Profit
4) Create a calculated field as below
if [Paramater] = "Sales" then [Sales] else [Profit] end
5) Drag the Region onto Rows shelf, drag the above calculation into Column's shelf.
6) Right click on Parameter, click on "Show Parameter Con trol"
7) Now we can Select Either [Sales] or [Profit].
Note: As of now in Tableau we can select 1 Value max at a time.
1) Dynamically select the Colour.
Sol:
1) Create a Parameter “Colour” with the Following values like the below screen shots.
Green, Blue, Red, Orange
2) Drag Region onto Row Shelf, Sales onto Column Shelves.
3) Create a Calculated field like the below
Name: Dynamic Colour
Syntax:
case [Color]
when "Green" then "Green"
when "Blue" then "Blue"
when "Red" then "Red"
when "Orange" then "Orange"
end
---- ------ Here [Color] is the Parameter which was created in the 1st step.
4) Drag “Dynamic Colour” onto Colors mark.
5) Right click on “Color” parameter, click on “Show Parameter Control”.
6) Now select colours 1 by 1 from “Parameter” then assign right colours.
Parameter - Options:
1) If you to show paramater values from any specific column, Select List Radio Button
from Alloawable Options then select Add from Field, then select the Field.
2) If you want to create a Parameter on specific column, right click on that,
click on "Create Parameter".
Updating Values in Quick Filters, Parameters:
If you are creating Quick Filter on any specific field, it will show all possible values as per current data availability. (Distinct Values).
Once you update database, Quick filter won't update directly, we have to use one option on Menu Bar, select "Auto updates Quick filters".
Updating Parameter Values:
Values of Parameter will not be changed once Database has been updated, So manually we have to open the Parameter, then Reload the values from that Specific field.
*********************************************************
Group -
A group is a combination of dimension members that make higher level categories.
EX: 1) We are having data in columns Country, State, City, we want to create
Region it is between State & Country.
We create Regions by using group mechanism on States.
2) Create a group of Customer Name Field,
All customers whose names are starts with "A", Define name "A"
Similarly All customers whose names are starting with "B", Define name "B" ........
***********************************************************
Sets -
- are custom fields that define a subset of data based on some
conditions. Computed sets update as data is being changed.
-> Alternatively, a set can be based on specific data point in your view.
Sets are sub set of data, which are derived from either only Dimension or combination
of Dimension & Measures.
EX:
1) Right click on Region, cick on "set".
2) Click on condition "By Field", Select the measure, define condition.(Sales>=10000)
3) Click on "OK".
4) Set has been created, it shows the list of customers whose sales are more than 10K.
EX: Combine sets
We can use the sets for set analysis.
Set 1 = Top 3 Customers
Set 2 = Bottom 3 Customrs
Combination of Top 3, Bottom 3
Set 1 Union Set2
***********************************************************
Bins:
Measures are columns that typically contain numeric data,
such are used to evaluate the BUS or to Analyze the BUZ.
EX: Sales , Profit.
We use the bins to categorize the measures into different groups by
using measure figures.
.Axes: Axes are created when you place a measure on the Rows or Columns shelf.
By default, the values of the measure field are displayed along
a continuous axis.
====================Day - 9============
1) ASCII:
It returns first letter of String's ASCII
ASCII(“Tableau”) = 84
2) Char: This function converts numerical number in to character.
EX: char(65)= A
3) Contains:
It will check whether substring is available or not in the main string.
It means it gives the Outputs as True/False
EX:
Region = South
contains([Region], "st") = True
contains([Region], "k") = False
4) ENDWITH:
It will check whether 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 substring in the main string.
It means it gives the Outputs as Numerical Numbers.
Region = South
Find ([Region], "th") = True
Find ([Region], "k") = False
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 is used to test the given string data,
if given data is date then returns “True” else false.
EX: isdate(‘[Order_Date]’) = True
Isdate(’12-Feb-2011’) = 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)
Region = South
Left([Region],2) = So
9) LEN:
This function count the number of characters including nulls & returns Numerical number.
10) MAX: Test & returns Maximum as output.
Max(“A”, “B”)= B
Max(“AAAA”, “D”)= D
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 sub string from specific point
to specific point.
EX: Region = South
Mid( [Region], 2,4) = out
13) Replace: This function is used to replace the substring by other substring.
EX: replace([Region], “th”, “th Carolina”) = South Carolina
14) Right: this function fetch the data up to specified number of positions
from right side.
15) RTRIM: this function removes empty space after the word,
which means right side empty space.
16) Space: This function is used to add the space in the word &
defined number of times.
17) Stars with: This function test whether string stars with specified substring, &
returns Boolean.
18) Trim: This function removes extra space of string for left &
right edges of the given string.
19) Upper: It converts the given string into Upper Case.
============================Day- 10==============================
In today’s session we will discuss about 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
Usage of this Function: 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#)
O/P: 12-Mar-2014
This View: Scenario:
Generally once Place the Order, we deliver after 2 Months,
So i want to see Deliver Date for Each Order.
Process:
1) Right click on [Order Date], click on "Create Calculated Field"
2) Define the Name, Syntax as like below.
Name: Deliver Date
Syntax: dateadd('month', 2, [Order Date])
3) Then add the [Order Date], [Deliver Date] to report.
4) Now 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.
*************
Usage of this Function: 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 Dimension window.
EX: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 like 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.
***************************
Usage of this Function: Datename
We use this function to extract specific part of the Date, It gives O/P as string Date Type.
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
**************
Usage of this Function: 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#
***********
Usage of this Function: Datetrunc
We use this function to get the First Date of given Date at 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
This View: Scenario:
I want to see what is the Day of the Year for Each Transaction ( Days 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 Work Area.
***************
Usage of this Function: Day, Month, Year
-> We use thse functions to extracts the Specific part from the Date, It means Year, Day, Month. And it gives Integer as Output.
-> Once you created calculated field using any of these functions, that will be moved into 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.
==================DAy -10====================
Table Calculations:
Table calculations are computations that are applied to the values in the table.
These Computations are unique in that they use data from multiple rows in the database to calculate a value. To create a table calculation, you need to define both what values you want to compute and what values to compute along. These are defined in the Table Calculation dialog box using the Calculation Type and Calculate Along drop-down menus.
Note:
Table Calculations are applied to 1 Measure at a time, if we want to apply on multiple measures we need to apply multiple times.
Key Notes & Definitions:
Addressing & Partitions are two parts in the Table Calculations.
Addressing:
The dimensions that define the part of the table you are applying the calculation to are called addressing fields.
The fields which are using for Calculation purpose.
Partitions:
The Dimension which are used to perform the groupings are called “Partition”.
The foelds which are using for Grouping purpose.
Table Down:
Calculation will take place from top to bottom (Vertically).
Table Across:
Calculation will take place from Left to Right (Horizontally).
Here we can perform different kind of Calculations.
--------------------Classifying Fields - Discrete vs Continuous ----------------------
Discrete vs Continuous:
Discrete: Dimension - Blue Color
Continuous:Measure - Green Color
Date Filed Discrete - as Dimension & Bar Graphs
Date Filed Continuous - as Measure & Line chart
Granularity - Level detail of data
Date: Year(Low Granularity) -> Quarter -> Month -> Week -> Day(High Granularity)
Date: Country(Low Granularity) -> Region -> State -> City(High Granularity)
--------------------Classifying Fields - Discrete vs Continuous ----------------------
No comments:
Post a Comment