Tableau Lecture 6

--- title: Lecture Agenda description: duration: 5400 card_type: cue_card --- ## Tableau Lecture 6: Table Calculation and Analytics #### Agenda * Table Calculations * Tooltip * Reference Lines * Trend Lines Dataset: [sample superstore](https://docs.google.com/spreadsheets/d/15W0gVPEHCDDnrKHpgj9XlUTIpNJsS9qX/edit?usp=share_link&ouid=116847520093376081608&rtpof=true&sd=true) --- title: Table Calculations description: duration: 5400 card_type: cue_card --- ### Table Calculations * Table calculations are a special type of calculated field within Tableau that apply transformations (i.e. additional math) on values within a visualization. * When you add a table calculation, you must use all dimensions in the level of detail either for partitioning (scoping) or for addressing (direction). * **Scope** - it defines the boundaries within which a given table calculations can reference other values * Scope options-Table,pane,cell * **Direction** - defines how the table calculations moves within the scope * Direction options-down,across,down then across,across then down * **Index** -Returns the index of the current row in the partition ### Index problem: Table calculation using Index to explain the concept. **Steps** 1. Create a calculated field index as shown below. <img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/045/929/original/index.png?1693807802 height=400 width=700> 2. Explain types of “compute using” options using index calculation. * Drag Order Date to Rows shelf * Click on the small plus sign to get the quarters for each year * Drag Region and Category to Columns shelf * Drag index to label in Marks card * Open the index drop down in the Marks card and try different 'compute using' options. <img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/045/931/original/cu.png?1693808391 height=400 width=700> ### Direction and Scope * Here we define which dimension in the view define direction(addressing) and all others define the scope(partitioning) <img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/045/932/original/dir.png?1693808536 height=200 width=500> \ **Steps** * After performing the steps for explaining 'compute using', open the index dropdown in menu card and select 'Edit Table Calculation...' We can notice that tableau is computing index in the direction of checked dimension category thus each new category increments the index. <img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/045/965/original/tbl.png?1693820557 height=400 width=700> ### Table calculations “compute using” * Table across * Table down * Table across then down * Table down then across * Pane across * Pane down * Pane across then down * Pane down then across * Cell-computation is done within a single cell --- title: Quiz-1 description: duration: 45 card_type: quiz_card --- # Question In Table Calculation dialog box, selecting one or more dimensions determines which of the following? # Choices - [ ] Direction - [ ] Scope - [x] Both - [ ] None of them --- title: Table Calculation Types description: duration: 5400 card_type: cue_card --- ### Table Calculation Types * Difference from - computes difference between current value and another value in the table * Percentage difference from - computes difference between current value and another value in the table as a percentage * Percentage from - computes a value as a percentage of some other value * Percent of total - computes a value as a percentage of all values in the current partition * Rank - computes a ranking for each value in a partition * Percentile - computes a percentile rank for each value in a partition * Running total - it aggregates values cumulative in a partition * Moving calculation - it determines the value by performing aggregation across specified number of values before or after current value ### Percent of total #### Business problem 1: Find percentage of total sales each month makes for each year. **Steps** 1. Creating total sales per month for the year 2014 to 2017. * Drag Order Date to Rows shelf * From dropdown of Order Date in Rows shelf, select Quarter * Click on the small plus on Quarter(Order Date) to get months * Drag Order Date to Columns shelf * Drag sales to labels in Marks card <img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/045/968/original/b1_1.png?1693820813 height=400 width=700> 2. Creating table calculation and solving business problem. * From sales dropdown in marks card, click 'Add Table Calculation...' * In calculation type choose 'Percent of total' * In 'compute using', choose Table(down) <img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/045/970/original/add_tbl.png?1693821203 height=400 width=700> * We can see for the year 2017 highest sales was done in the month of November. ### Rank For each year and for each quarter we are finding a **rank based on sales** so we can see below that for all the years in quarter1 March month had highest sales and hence it has rank=1 . **Steps** * After performing the steps of previous business problem, click 'Edit Table Calculation...' in sales dropdown in Marks card. * Change calculation type to Rank * Change 'compute using' to Pane(down) <img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/045/973/original/rank_2.png?1693821999 height=400 width=700> Reference for [Table Calculation Types](https://help.tableau.com/current/pro/desktop/en-us/calculations_tablecalculations_definebasic_runningtotal.htm#:~:text=Copyright-,Table%20Calculation%20Types,-Applies%20to%3A%20Tableau) ### Table Calculations functions: Index vs Rank <img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/045/975/original/ivr.png?1693822418 height=400 width=700> --- title: Quiz-2 description: duration: 60 card_type: quiz_card --- # Question You have the monthly sales of a store. You want to find the month on month change in sales. Which of the following table calculations can be used? # Choices - [ ] Running Total - [x] Difference From - [ ] Percentile - [ ] Rank --- title: Quick Table Calculation description: duration: 5400 card_type: cue_card --- ### Quick Table Calculation * It allows us to quickly apply a common table calculation to your visualization #### Business problem 2: Find percentage of total sales done across different categories for each quarter of the given year **Steps** * Drag Order Date to Rows shelf * Click on the small plus sign on Order Date to get Quarters * Drag Category to Columns shelf * Drag sales to label in Marks card * From sales dropdown in marks card, go to Quick Table Calculation and select 'Percent of Total' <img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/046/000/original/quick_1.png?1693826484 height=400 width=700> * Go to Analysis in menu bar, go to totals and select 'Show Row Grand Totals' * Again from Analysis menu, go to Total and select 'Add All Subtotals' <img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/045/999/original/quick.png?1693826211 height=400 width=700> * We can see that for the year 2016 for 1st quarter 42.31% of total sales was done in technology category and for the year 2016 also the highest sales was done by technology category. ### Difference between Calculated Field and Table Calculation * **Table calculations** are created in a tableau view and stay there locally in our worksheet. * **Calculated fields** are created on a data level and appear as a separate column in the data source. <img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/046/003/original/diff.png?1693826734 height=250 width=400> [Image Courtesy](https://www.thedataschool.co.uk/elena-caric/difference-table-calculations-calculated-fields) #### Tableau Functions post-read > You can share the following post-read with learners: https://docs.google.com/document/d/19DMOPNkhEG3Z10OXP203aCvmSUQOBsPF/edit?usp=sharing&ouid=100659516601446935794&rtpof=true&sd=true --- title: Tooltip description: duration: 5400 card_type: cue_card --- ### Tooltip Tooltips are additional data details that display when you hover over one or more marks in the view. Tooltip command button for exploring data- * Keep only - creates a filter that removes all the other data. * Exclude - creates a filter that removes the selected data. * Group members - creates a group based on the selection. * Create set - creating a new set containing the selected members. * View data - opens a window displaying the data. #### Business problem 3: Create a map visualisation that displays profit for each state and allows the user to view profit by subcategory for that state in the tooltip when they hover over a state. **Steps** 1. Creating a source sheet which plots profit by each state in a map visualization * Drag Latitude to rows shelf * Drag Longitude to columns shelf * Drag State to Details in marks card * Click on the '49 unknown' box on the bottom right corner * Click on Edit Locations and change Country from India to 'From Field' Country. <img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/046/055/original/states.png?1693834967 height=400 width=700> * Change graph type to map * Drag Profit to color in marks card * Drag State to label in marks card * Change the sheet name to source sheet <img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/046/056/original/states_profit.png?1693835246 height=400 width=700> 2. Creating a target sheet which plots subcategory wise profit. * Drag sub-category to rows shelf * Drag Profit to Columns shelf * Drag profit to color in marks card * Change the sheet name to target sheet <img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/046/057/original/target_sheet.png?1693835358 height=400 width=700> 3. Adding target sheet visualization in the tooltip. * In the source sheet, click on tooltip in marks card * Click on Insert > Sheet > target sheet * Hover over different states to see the result <img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/046/061/original/insert_tt.png?1693835802 height=400 width=700> * We can see that most of the product subcategories are losing money in texas 4. Editing the size of the tooltip visualization * Click on tooltip in marks card * Change max-width and max-height to 500. <img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/046/065/original/500.png?1693836081 height=400 width=700> --- title: Quiz-3 description: duration: 30 card_type: quiz_card --- # Question Do Table Calculations appear in the Data Source? # Choices - [ ] Yes - [x] No - [ ] Depends on the type of table calculation --- title: Quiz-4 description: duration: 45 card_type: quiz_card --- # Question In the previous business problem, we added the target sheet to the tooltip of the source sheet. Is it possible to add the source sheet to the tooltip of the target sheet? # Choices - [x] Yes - [ ] No - [ ] Depends on the content of the sheet --- title: Reference Line description: duration: 5400 card_type: cue_card --- # Reference Line * It is simply a line that gets drawn on a chart that represents another measure or point of reference. #### Business problem 4: Find subcategories with above-average sales for each category and year. * Drag Order Date, Category and sub-category to columns shelf * Drag sales to rows shelf * Go to the Analytics tab in the left pane * Drag reference line to the pane option * Choose color and opacity of line and click ok. <img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/046/068/original/reference.png?1693836790 height=400 width=700> ### Controlling Reference Line with Parameters #### Business problem 5: You want to decide the sales target for next year based on this year profit\*target percentage with the flexibility to decide the target percentage. **Steps** 1. Creating a basic view that displays total sales and a target field that will be displayed using a reference line in the view. * Drag sales to rows shelf * Create a claculated field 'target' using calculation sum([Profit]) * Drop target on detail in marks card * Go to the Analytics tab and create a 'table' reference line. * Choose value as AGG(target), measure as SUM, click OK. <img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/046/097/original/ref_2.png?1693844513 height=400 width=700> 2. Creating a parameter * From target dropdown in the Data panel, choose Creat > Parameter... * Name the parameter 'Adjustable Parameter', set its minimum value to 0 and maximum value to 100, set current value to 0, display format to percentage. Click OK. <img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/046/099/original/ref_param.png?1693845082 height=400 width=700> 3. Tying up the parameter with the target field and solving the problem. * Change the formula of target to -> SUM([Sales]) * [Adjustable Parameter] * From the dropdown of Adjustable Parameter click 'Show Parameter' * Change the value Adjustable Parameter in the right pane to change the target. <img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/046/105/original/param_ref.png?1693845682 height=400 width=700> --- title: Quiz-5 description: duration: 45 card_type: quiz_card --- # Question What kind of reference line can we add to a visualization? # Choices - [ ] Average - [ ] Minimum, Maximum - [ ] Constant - [x] All of the above --- title: Data Mining description: duration: 5400 card_type: cue_card --- ### Trend Lines : * Trend lines are used to predict the continuation of a certain trend of a variable. * It also helps to identify the correlation between two variables by observing the trend in both of them simultaneously. * There are many mathematical models for establishing trend lines. Tableau provides four options. They are Linear, Logarithmic, Exponential, and Polynomial * Tableau takes a time dimension and a measure field to create a Trend Line. ### Instructor Note: Trend Lines Trends are computed by Tableau after the query of the data source and are based on various elements in the view: * The two fields that define X and Y coordinates: The fields on Rows and Columns that define the x and y axes describe coordinates,allowing Tableau to calculate various trend models. In order to show trend lines, you must use a continuous (green) field on both Rows and Columns. The only exception to this rule is that you may use a discrete (blue) date field. If you use a discrete date field to define headers, the other field must be a continuous field. * Additional fields that create multiple, distinct trend lines: Discrete (blue) fields on the Rows, Columns, or Color shelves can be used as factors to split a single trend line into multiple, distinct trend lines. ### Instructor can cover briefly: **Intuition on different trendline models:** Consider example where x axis has size of a house in square feet and price on y axis * **Linear**: We'd use a linear model if we assumed that, as size increases, other price will increase at a constant rate * **Logarithmic**: We'd employ a logarithmic model if we expected the law of diminishing returns in effect—that is size can increase only so much before buyers will stop paying much more * **Exponential**: We'd use an exponential model to test the idea that each additional increase in size results in a dramatic (exponential!) increase in price * **Polynomial**: We'd employ a power trend model if we felt relation would indicate that the price was a function of the size to a certain power. * **Power**: We'd use this model if we felt the relationship between Size and Price was complex and followed more of an S-shaped curve Trend Line model terms : [link](https://help.tableau.com/current/pro/desktop/en-us/trendlines_add.htm#trend-line-model-terms) ### Business problem 6: Determine the year wise trend in sales. * Drag Order Date to columns * Drag Sales to rows * Go to Analytics tab, drag trend line to linear option. * Click on the trend line created, choose edit. * Tick the show confidence bands option * Check how the different trend lines fit the data. <img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/046/117/original/trend_1.png?1693847111 height=400 width=700> To get more detailed information like MSE, SSE, R-squared, p-value etc. go to Analytics > Trend Lines > Describe Trend Model. <img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/046/120/original/describe.png?1693847389 height=400 width=700> \ <span style="color: red;">**Important:**</span> Please inform the learners that they'll get the reference notes for today's lecture as a post-lecture content on their dashboard.