Tableau Lecture 4

--- title: Lecture Agenda description: duration: 5400 card_type: cue_card --- ## Tableau Lecture 4: Filters and Calculations #### Agenda * Charts * Stacked Bar chart * Scatter plot * Tree map * Combined-axis chart * Dual-axis chart * Filters * Types of Filter * Extract * Data Source * Context * Order of Filters * Calculations * Calculated Fields * Operators * Functions * Types of Calculation * Row-level * Aggregate-level #### Dataset : [sample superstore](https://docs.google.com/spreadsheets/d/1Dim0dtvQBWWvrqRRxTt0WLAI6VOJs_oe/edit#gid=2115366062) --- title: Stacked Bar Chart description: duration: 5400 card_type: cue_card --- ### Stacked Bar Chart * It is a simple bar chart with segmented bars. * It requires * 1 or more dimension and * 1 or more measure. * When you select dimension into the colour marks chart what you get is a stacked bar chart. #### Business problem 1: * Display total sales for each category by region. * Drag **Category** to Columns * Drag **Sales** to Rows * Add **Region** to Color <img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/045/003/original/lec4_bp1.png?1693377308 height=400 width=700> --- title: Quiz-1 description: duration: 45 card_type: quiz_card --- # Question What kind of data is Stacked Bar Chart useful for? # Choices - [ ] Numerical, Numerical - [x] Numerical, Categorical, Categorical - [ ] Categorical, Categorical --- title: Scatter Plot description: duration: 5400 card_type: cue_card --- ### Scatter Plot * It displays a data point at their respective intersection of both the measures * We can create a scatter plot by adding at least one measure on row shelf and one measure on column shelf #### Business problem 2: * Find order id that has the highest sales and highest profit values. * Creating a scatter plot what we see below is the total sales and total profit. * Drag **Profit** to Rows * Drag **Sales** to Columns <img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/045/024/original/lec_4_bp_2.png?1693379145 height=400 width=700> * Disaggregating the measures to plot every row value in the plot and solving the business problem. * Click on Analysis in Menu bar * Uncheck the Aggregate Measures option <img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/045/028/original/lec_4_bp_2_2.png?1693379495 height=400 width=700> --- title: Quiz-2 description: duration: 45 card_type: quiz_card --- # Question What kind of data is a Scatter Plot useful for? # Choices - [x] Numerical, Numerical - [ ] Numerical, Categorical - [ ] Categorical, Categorical --- title: Tree Map description: duration: 5400 card_type: cue_card --- ### Tree Map * It is a visualization that nests rectangles in hierarchies. * It uses dimension to define the structure of the tree map and measure to define the size or color of each of the rectangle. * It requires one or more dimensions and one or 2 measures. #### Business problem 3: * Find the sub-category that has the highest sales and highest profit. * Add **Sub-Category** to Detail * Add **Sub-Category** to Label * Add **Sales** to Size * Add **Profit** to Color <img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/045/034/original/l4_bp3.png?1693380246 height=400 width=700> --- title: Quiz-3 description: duration: 45 card_type: quiz_card --- # Question What kind of data is Tree Map useful for? # Choices - [ ] Numerical, Numerical, Numerical - [x] Numerical, Categorical - [ ] Categorical, Categorical --- title: Combined & Dual Axis Charts description: duration: 5400 card_type: cue_card --- ### Combined Axis Chart * Also called a **Blended-axis** or **Shared-axis** chart. * Measures share a single axis so that all the marks are shown in a single pane. * They are used to compare measures of same data type and same scale. * Only single mark card is present and we can compare more than two measures. * To create a combined-axis chart, drag the measure on the left side of the already existing axis. * It requires one or more dimension and two or more measures. #### Business problem 4: * Compare sales and profit for each category in a single plot. * Drag **Category** to Columns * Drag **Sales** to Rows * Drag **Profit** to vertical axis of the chart <img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/045/046/original/l4_bp4_1.png?1693381056 height=400 width=700> * Add Measure Names to Color * Change the Fit from Standard to Entire View <img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/045/051/original/l4_bp4_2.png?1693381526 height=400 width=700> ### Dual Axes Charts * You can compare multiple measures using dual axes, which are two independent axes that are layered on top of each other. * Dual axes are useful for analyzing two measures with different scales. * Can’t compare more than two measures. * To create a dual-axis chart, we can drag the second measure to right side of the plot to create another axis or right click on the second measure and select dual axis. * It requires two measures and at least one dimension #### Business problem 5: * Find sub-category that has the lowest discount but highest profit. * Drag **Sub-Category** to Columns * Drag **Profit** to Rows * Drag **Discount** to Rows * Open the **Discount** field dropdown and choose Dual Axis <img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/045/284/original/l4_bp5_1.png?1693480440 height=400 width=700> * In the Marks card shelf, change plot type of **Profit** to Bar. * In the Marks card shelf, change plot type of **Discount** to Line. <img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/045/286/original/l4_bp5_2.png?1693480699 height=400 width=700> --- title: Filters and their types description: duration: 5400 card_type: cue_card --- ## Filters * Filter removes some scope of data from a data set. * Filters are very helpful to create dashboards in Tableau. * Filters can help to minimize the size of data sets for efficient use, eliminate irrelevant dimension elements, clean up underlying data, set date ranges and measures as required, simplify and organize data, etc. ### 1. Extract Filters * Extract filter in Tableau are used to extract a small subset of data from the original data source. * Tableau then creates a local copy of the data set that is to be stored in the repository. * These methods reduce Tableau queries. * The data size can be further reduced by applying the measure or dimension filter to the extract as required. * Unlike Tableau Desktop(paid) which supports both extract and live connections, Tableau public, by default, only supports extract. **Important Note:** * Tableau public always works with extracts, and therefore does not show the options ‘Live’ and ‘Extract’. * Here is a screenshot of Tableau Desktop Professional that supports both extract and live connections. <img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/045/288/original/extract.png?1693481317 height=400 width=700> ### 2. Data Source Filters * Data source filters in Tableau are mainly used to restrict sensitive data from viewers and reduce data feeds. * Viewers can, however, have certain access rights to view the underlying data. * Data source filters allow the direct application to source data. * One important thing to mention is that the extract filter and the data source filter are not linked, and if you happen to go back to a live connection, the data source filter will remain intact. #### Business Problem 6 : Show orders which made profit >= 1K. **Steps:** * Go to the Data Source tab * Click on Add Filter on the top right * Click on Add... * Select **Profit** from the list of fields * Set the minimum value to 1000 <img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/045/289/original/filter.png?1693482110 height=400 width=700> ### 3. 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. * The context filter adds an actionable context to data analysis, but if the data is not reduced enough, the cost of computing can be very high. #### Business Problem 7 : Find the top 10 states in the west region with the highest sales. **Steps:** * Drag **State** to Rows * Drag **Sales** to Columns * Click on Sort Descending <img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/045/291/original/bp7_1.png?1693482875 height=400 width=700> * Put **State** into Filters * Click on the Top tab of Filter dialog box * Choose Top 10 by **Sales** <img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/045/293/original/bp7_2.png?1693483857 height=400 width=700> * Drag **Region** to Rows * Put **Region** into Filters * De-select all regions, then Select West <img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/045/295/original/bp7_3.png?1693484115 height=400 width=700> * Right click on **Region** in Filters card, select Add to Context. <img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/045/396/original/bp_7_add.png?1693547713 height=400 width=700> \ **Explanation:** 1. First we filter out top 10 states by sales 1. Next when we apply filter for region=West 1. We only see 2 entries that instead of 10 that is because top 10 filter gets executed first and then the region filter is executed if you see from the 10 states only 2 states belong to west region 1. Now in order to change the order of operation we add region filter to context filter this ensures that first our region filter gets executed and then the top 10 by sales is executed. ### 4. Filters on Dimensions (whether on the Filters shelf or in filter cards in the view) * Dimension filters in Tableau are non-aggregated filters. The dimensions that are used are mostly blue pills. Blue pills correspond to discrete data. * If there are many dimensions, one can search for them. Dimension filter provides four options, General, Wildcard, Condition, and Top/Bottom. You can pick up any of the four options to select the right data or remove the unwanted data. * One can create their own formula as well and then use it in the Condition filter and the Top/Bottom filter for data selection. They provide a channel to measure to get the required data. #### Business Problem 8: Show ship mode and sub-categories w.r.t. profit where sub-categories - labels and storage are excluded. **Steps:** * Drag **Ship Mode** to Rows * Drag **Sub-Category** to Rows * Drag **Profit** to Columns * Put **Sub-Category** into Filters * De-select all boxes and Select Labels and Storage * Check the Exclude box and click OK. <img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/045/297/original/bp_8.png?1693484811 height=400 width=700> ### 5. Filters on Measures (whether on the Filters shelf or in filter cards in the view) * Using a Measure filter in Tableau allows for various operations and aggregate functions such as sum, median, avg, standard deviation, etc. * Aggregated filters are always applied after non-aggregated filters, no matter what the order is on the Filters pane. * The filters are applied to Measure fields consisting of quantitative data. In a subsequent dialog box, you will get four types of filters: 1. **Range**: Select the range of values to include in the result 1. **At least**: Select the minimum value of a measure 1. **At most**: Select the maximum value of a measure 1. **Special**: Select null or non-null values #### Business Problem 9 : Show only the sub-categories whose average profit is greater than 20. **Steps:** * Drag **Profit** to Columns * Change aggregation of **Profit** from Sum to Average * Drag **Sub-Category** to Rows * Put **Profit** into Filters * Change the minimum value to 20 and click OK <img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/045/298/original/bp_9.png?1693485445 height=400 width=700> --- title: Order of Filters description: duration: 5400 card_type: cue_card --- ### Order of Filters Tableau performs actions on our view in a very specific order; this is called the Order of Operations. It helps you avoid filter conflicts and achieve efficiency with your dashboard. Filters are executed in the following order: 1. Extract filters 1. Data source filters 1. Context filters 1. Filters on dimensions (whether on the Filters shelf or in filter cards in the view) 1. Filters on measures (whether on the Filters shelf or in filter cards in the view) 1. Table Calculation filter <img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/045/299/original/order.png?1693485672 height=350 width=600> \ `Instructor can ask the following questions:` 1. When we saw context filter example before adding a filter to context which filter would be executed first? * The top N filter executes first and then the dimension filter. 3. Now after adding to context what is happening? * The context filter executes first and then the top N filter executes. * We are forcing a certain filter to be executed first. --- title: Quiz-4 description: duration: 45 card_type: quiz_card --- # Question What's the correct order of operation of extract, data source and context filters? # Choices - [ ] Data Source, then Extract, then Context - [x] Extract, then Data Source, then Context - [ ] Context, then Extract, then Data SOurce --- title: Calculations in Tableau description: duration: 5400 card_type: cue_card --- There are four basic components to calculations in Tableau: * **Functions** - Statements used to transform the values or members in a field. * **Fields** - Dimensions or Measures (columns) from your data source. * **Operators** - Symbols that denote an operation. * **Literal expressions** - Constant values that are represented “as is”, such as "Profitable" and "Unprofitable". ### 1. Calculated Fields * When you create a calculated field, you are essentially creating a new field (or column) in your data source, the values or members of which are determined by a calculation that you control. * This new calculated field is saved to your data source in Tableau, and can be used to create more robust visualizations. You can use calculated fields for many reasons. Some of the examples could be: * 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 ### 2. Operators in Tableau We have mainly 3 types of operators in Tableau. * Arithmetic operator * Some of the commonly used arithmetic operators are addition, subtraction, division, multiplication, modulo * E.g. `profit+sales`, `profit/sales` * Comparison operator * Some of the commonly used comparison operators are ==,>,<,>=,<=,!= * E.g. `sales>100k`, `profit<=1k` * Logical operator * Some of the commonly used logical operators are AND, OR, NOT * E.g. `profit=100 AND sales=1000`, `NOT(sales=profit)` Order of Precedence of Operators : [Link](https://help.tableau.com/current/pro/desktop/en-us/functions_operators.htm) ### 3. Tableau Functions **Instructor Note:** Pick and explain any 2 of these functions and give the rest as Homework. Tableau supports a variety of functions which have been organized category-wise below : * [Numeric functions](https://help.tableau.com/current/pro/desktop/en-us/functions_functions_number.htm) : Number functions allow you to perform computations on the data values in your fields. Number functions can only be used with fields that contain numerical values. * [String functions](https://help.tableau.com/current/pro/desktop/en-us/functions_functions_string.htm) : String functions allow you to manipulate string data (i.e. data made of text). * [Date functions](https://help.tableau.com/current/pro/desktop/en-us/functions_functions_date.htm) : Date functions allow you to manipulate dates in your data source. * [Type conversion](https://help.tableau.com/current/pro/desktop/en-us/functions_functions_typeconversion.htm) : Type conversion functions allow you to convert fields from one data type to another. * [Logical functions](https://help.tableau.com/current/pro/desktop/en-us/functions_functions_logical.htm) : Logical calculations allow you to determine if a certain condition is true or false (boolean logic). * [Aggregate functions](https://help.tableau.com/current/pro/desktop/en-us/calculations_calculatedfields_aggregate_create.htm) : Aggregate functions allow you to summarize or change the granularity of your data. * [User functions](https://help.tableau.com/current/pro/desktop/en-us/functions_functions_user.htm) : User functions can be used to create user filters or row-level security(RLS) filters that affect visualizations published to Tableau Server or Tableau Online, so that only certain people can see your visualization. Not available in Tableau Public. * [Table calculation functions](https://help.tableau.com/current/pro/desktop/en-us/functions_functions_tablecalculation.htm): Table calculation functions allow you to perform computations on values in a table. * [Spatial functions](https://help.tableau.com/current/pro/desktop/en-us/functions_functions_spatial.htm) : Spatial functions allow you to perform advanced spatial analysis and combine spatial files with data in other formats like text files or spreadsheets. * [Additional functions](https://help.tableau.com/current/pro/desktop/en-us/functions_functions_additional.htm) : * Regular Expressions * Hadoop Hive specific functions * Google BigQuery specific functions --- title: Types of Calculations description: duration: 5400 card_type: cue_card --- ### Types of Calculations There are three main types of calculations you can use to create calculated fields in Tableau: * Basic expressions : They allow you to transform values or members at the : * Data Source level of detail (a row-level calculation) or * At the visualization level of detail (an aggregate calculation). * Level of Detail (LoD) expressions : * They too allow you to compute values at the data source and the visualization level. * LoD expressions give you even more control (compared to basic expressions) on the level of granularity that you want. * They can be performed at a more granular level (INCLUDE), a less granular level (EXCLUDE), or an entirely independent level (FIXED). * Table calculations : They allow you to transform values at the level of detail of the visualization only and are added to measures. **Note:** 1. In this lecture we will cover basic expressions 1. LoD calculations & Table calculations will be covered in the next lecture. ### Row-level Calculations Calculations are performed for every row of underlying data. #### Business problem 10 Find average cost of each product sub-categories. `(cost=Sales-Profit)` * Go to Analysis and click on Create Calculated Field... * Enter the formula - `[Sales] - [Profit]` * Rename the new field as "Cost" and click OK. <img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/045/304/original/bp_10.png?1693489424 height=400 width=700> * We can verify that it is done for each row by clicking on view data (near the search bar in left pane), we can see a new column added “cost”. <img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/045/306/original/bp_10_2.png?1693489966 height=400 width=700> \ **Note:** Subtraction arithmetic operator is used in this calculation. #### Solving the business problem: * Drag **Sub-Categor**y to Rows * Drag **Cost** to Label in Marks shelf * Change aggregation of **Cost** from Sum to Average <img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/045/307/original/bp_10_3.png?1693490221 height=400 width=700> ### Aggregate-level Calculations Calculations are performed at an an aggregate level which is defined by the dimension used in the view. #### Business problem 11 Find profit ratio of each product sub categories Profit ratio=profit/sales. * Go to Analysis and click on Create Calculated Field... * Enter the formula - `SUM([Profit])/SUM([Sales])` * Rename the new field as "Profit Ratio" and click OK. <img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/045/314/original/bp_11_1.png?1693493301 height=400 width=700> \ **Note:** Division arithmetic operator is used in this calculation. #### Solving the business problem: * Drag **Sub-Category** to Rows * Add **Profit** to the Label, next to **Sub-Category** * Drag **Sales** to the **Profit** column * Drag **Profit Ratio** to the **Sales** column * From the Measure Values card, change the format of **Profit Ratio** to Percentage <img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/045/315/original/bp_11_2.png?1693494198 height=400 width=700> \ **Note:** * Notice AGG in Profit Ratio. Refer to this [link](https://help.tableau.com/current/pro/desktop/en-us/calculations_calculatedfields_lod_overview.htm#:~:text=This%20calculation%20will,an%20AGG%C2%A0function%3A). * Here, the SUM is performed first based on the dimension sub-category (i.e. sum of profit and sum of sales for each sub-category) and then the division occurs. * Now if we change the dimension from sub-category to category, the calculation will be done based on that. \ **Important:** Please inform the learners that they'll get the reference notes for today's lecture as a post-lecture content on their dashboard. --- title: Cascading, Interactive & Date Filters [EXTRA] description: duration: 5400 card_type: cue_card --- `Instructor can cover the following topics if time permits.` ### Cascading Filter #### Business problem : Using a cascading filter, find out the Category & Sub-Category wise Sales for a particular Region, State, and City. **Demo:** * Drag **Category** & **Sub-Category** to Rows * Drag **Sales** to Columns * Add **Category** to Color * Go to dropdown for **Region** and click on Show Filter * Do the same for **State** and **City** fields * For **Region** filter, select Single Value (list) * For **Region** in Filters shelf, select Add to Context * For **State** filter, select All Values in Context * For **City** filter, select Only Relevant Values <img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/059/252/original/img.png?1702398918 height=400 width=700> ### Interactive Filter When an interactive filter is shown, you can quickly include or exclude data in the view. **Demo:** * Drag **Sub-Category** to Rows * Drag **Sales** to Columns * Open **Sub-Category** dropdown and select Sort * Sort by **Sales** in descending order * In the **Sub-Category** dropdown, click on Show Filter * Choose the desired sub-categories from the right pane <img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/045/349/original/interactive.png?1693501222 height=400 width=700> ### Date Filter * When you drag a date field from the Data pane to the Filters shelf, Filter field dialog box appears. * You can select whether you want to : * filter on a relative date; * filter between a range of dates; or * select discrete dates or individual dates to filter from the view. #### Business problem : Show Profit w.r.t. Order Date (where date is a continuous dimension). **Demo:** * Drag **Order Date** to Columns * Drag **Profit** to Rows * Convert **Order Date** field to Continuous * Put **Order Date** into Filters * Choose the Range of Dates <img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/045/352/original/last_bp.png?1693502267 height=400 width=700>