Defining relationships among the tables and applying filter.

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 Orders, People, Return tables are selected for reporting.
d. Tableau Engine defines the Relationships automatically. Although we can also define our own relationships.(IT IS EQUALENT TO V-LOOKUP INEXCEL)

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.

Axis

Axis is 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

Bins

Bins:
Measures are columns that typically contain numeric data,
such are used to evaluate the BUS or to Analyse the BUZ.
EX: Sales , Profit.
We use the bins to categorise the measures into different groups by
using measure figures.

Sets -

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

Group -

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" ........

Updating Values in Quick Filters, Parameters:

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.

Parameters

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".

Adding external Images to the Report

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. (In this example, Home Images are downloaded from GOOGLE)

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.

Changing Default Properties:

Changing Default Properties:

-> We can change  the Default properties like Colour, 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.

Hierarchies:

Hierarchies:
We use Hierarchies to organise the data
from Top Level information to Bottom level Information.
Top Level Information hold the Summary data, Bottom Level holds detail level data.
Sum up the bottom 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.

Conditional Statements in Tableau

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."

Tableau offering writing the conditions using the following approaches
1) IF
2) CASE

1) If condition with 1 Possible value  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

1) I want to Apply Different colours to the 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 Calculations to filter the multiple values 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

Quick Filters:

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 on Measures
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.

Filters - Measures

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..........

Filters

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.

Sales Tree- Tableau

1) Sales Tree has been created using the following data set.

2) In this step we are going to show Total Sales Volume with

combination of all Dimensions as like below image.

3) Next 2 steps tells about how to model t he data.

3.1) Add new Field the "Sales Tree" with the values "S.T.". (Data Model sheet -1)

3.2) Create a new sheet as like below image. (Data Model sheet -2)

4) Now there are having 2 Sheets in our Data Model Set apart from Underline Data set(Actual data set).

5) Now Connect Tableau to the above Data set

.

6) Create 5 calculated fields as below.

Name: 1st

Syntax: RUNNING_SUM(1/(Size()+1))

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

Name: 2nd

Syntax: RUNNING_SUM(1/(Size()+1))

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

 Name: 3rd

Syntax: RUNNING_SUM(1/(Size()+1))

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

Name: Temp

Syntax: 1/(1+EXP(1)^-[Sizeofline])

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

Name: Tree

Syntax: [1st]+([2nd] - [1st])*ATTR([Temp])

7) Now Drag the "Tree", "Sizeofline" Fields onto Column, Row shelf Respectively.

8) Right click on "Sizeofline" field on the rows shelf then convert into Dimension.

9) Drag the "Dimension" onto Detail Shelf.

then Right click on "Tree" then Define the Calculation as below.

10) Now report get changes as below.

11) Now prepare 2 Separate Reports, where 1 is for Summary (Dimension Name) 2nd is for detail (Dimension Values ).

13) Now Add the Dimension Name, Dimension Values, Tree Reports to the Dashboard.

14) And apply the Filter to all Reports.

We can build different kind of List, Cross-tab, Graphs and Maps Reports using Tableau Software.

We can build different kind of List, Cross-tab, 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(ZOOM IN) 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.