MCA 3 - TableAU Calculations, Sort and Filter with TableAU for IT Students

 Prepared By : Prof. Uday Shah (HOD - IT)

TableAU Calculations, Sort and Filter with TableAU

1. TableAU Calculations

• Definition: Tableau calculations are expressions written using Tableau’s syntax to manipulate, transform, or derive new values from existing data fields.

• Purpose: They allow you to create new measures or dimensions without changing the underlying source data.

• Types: Calculated Fields, Table Calculations, Level of Detail (LOD) Expressions.

• Flexibility: You can use arithmetic, logical, string, and date functions in calculations.

• Live Update: When the source data changes, calculated fields update automatically.

• Dynamic: They can be interactive with filters and parameters.

• Customization: Tailor data analysis beyond built-in functions.

• Reusability: Once created, they can be reused across multiple worksheets.

• Performance: Simple calculations are fast, but complex nested LODs may slow dashboards.

• Integration: Works with both live and extracted data sources.

• Output: Can return text, number, date, Boolean, or aggregated values.

• Example: Creating a “Profit Margin” field.

🔹 Practical Exercise:

• Formula:

  [Profit Margin] = ([Profit] / [Sales]) * 100
  

• Steps in Tableau:

  1. Open Global Superstore in Tableau.

  2. Go to Analysis → Create Calculated Field.

  3. Name it Profit Margin.

  4. Enter the above formula.

  5. Place it in Columns with Category in Rows.

• Expected Output: Bar chart showing Profit Margin % for each category.

2. TableAU Operators

• Definition: Operators are symbols that perform operations like arithmetic, comparison, and logical evaluations.

• Categories: Arithmetic (+ - * / %), Comparison (= != > < >= <=), Logical (AND OR NOT).

• Usage: Combine operators with fields for custom logic.

• Case Sensitivity: Operators themselves are not case sensitive, but string values are.

• Importance: Essential for IF statements, filtering, and conditional logic.

• Combination: Can be nested inside functions.

• Error Handling: Incorrect type matching (e.g., adding a string to a number) causes errors.

• Performance: Simple operators execute instantly.

• Practicality: Often used for ranking, segmentation, or data cleaning.

• Example: [Profit] > 0 AND [Region] = "Asia" returns only profitable Asian sales.

• Flexibility: You can use them in calculated fields or directly in filter conditions.

• Testing: Test small calculations before applying to large dashboards.

🔹 Practical Exercise:

• Create a field:

  [High Profit Asia] = IF [Profit] > 0 AND [Region] = "Asia" THEN "Yes" ELSE "No" END
  

• Drag it to Filters → Show only "Yes".

• Output: Visualization showing only profitable orders from Asia.

3. TableAU Functions

• Definition: Functions are predefined formulas that perform specific operations.

• Categories: String, Date, Number, Logical, Type Conversion, Aggregate, Window.

• String Example: UPPER([Customer Name]) → Makes names uppercase.

• Date Example: YEAR([Order Date]) → Extracts the year.

• Numeric Example: ROUND([Sales], 2) → Rounds sales to two decimal places.

• Logical Example: IF [Profit] > 0 THEN "Profit" ELSE "Loss" END.

• Aggregation Example: SUM([Sales]).

• Window Example: RUNNING_SUM(SUM([Sales])).

• Purpose: Increase flexibility in data manipulation.

• Usage: Can be nested inside other functions for complex analysis.

• Dynamic: Changes with filters and parameters.

• Testing: Use calculated field editor’s "Test" feature to check.

🔹 Practical Exercise:

• Create a field:

  Year-Sales = STR(YEAR([Order Date])) + " → " + STR(SUM([Sales]))
  

• Output: Display year along with total sales in one label.

4. Numeric Calculations

• Perform math operations like addition, subtraction, multiplication, division.

• Can aggregate (SUM, AVG, MIN, MAX) or transform (ROUND, CEILING, FLOOR).

• Example: Discounted Price = [Sales] - ([Sales] * [Discount]).

🔹 Practical Exercise:

• Calculate Discounted Price for each product.

• Output: Table showing product name and its discounted price.

5. String Calculations

• Manipulate text values (names, product IDs).

• Examples: LEFT, RIGHT, MID, CONCAT, UPPER, LOWER, TRIM.

• Use for data cleaning and formatting.

🔹 Practical Exercise:

• Create Customer Initials = LEFT([Customer Name], 1).

• Output: List of customers with their initials.

6. Date Calculations

• Extract date parts (DAY, MONTH, YEAR).

• Perform differences (DATEDIFF) or add intervals (DATEADD).

• Format dates (DATENAME).

🔹 Practical Exercise:

• Order Year = YEAR([Order Date]).

• Output: Chart showing sales per year.

7. Table Calculations

• Calculations done after aggregation (e.g., running totals, percent of total, rank).

• Dynamic based on table structure.

🔹 Practical Exercise:

• Apply Running Total to Sales.

• Output: Cumulative sales over time.

8. LOD Expressions

• Level of Detail: Control granularity of calculations.

• {FIXED [Region]: SUM([Sales])} ignores other filters.

• Useful for multi-level aggregation.

🔹 Practical Exercise:

• {FIXED [Category]: SUM([Profit])} → Profit per category regardless of sub-category filter.

9. Create Calculated Field

• Go to Analysis → Create Calculated Field.

• Enter name, formula, and validate.

🔹 Practical Exercise:

• Profit Ratio = [Profit] / SUM([Sales]).

• Output: Profit ratio per region.

10. IF Function

• Conditional logic: IF condition THEN result ELSE result END.

🔹 Practical Exercise:

• IF [Profit] >= 0 THEN "Profit" ELSE "Loss" END.

• Output: Color-coded profit/loss map.

11. CASE Function

• Simplifies multiple conditions.

🔹 Practical Exercise:

CASE [Region]
WHEN "Asia" THEN "A"
WHEN "Europe" THEN "E"
ELSE "Other"
END

• Output: Regions replaced with short codes.

📌 Sort and Filter with TableAU

12. Filtering in Visualization

• Filters limit data displayed.

• Can be applied to dimensions, measures, or both.

Practical: Filter orders where Sales > 500.

13. Basic Filters

• Directly drag field to Filters shelf.

Practical: Filter only "Furniture" category.

14. Quick Filter

• User-friendly on-screen filter.

Practical: Show Category quick filter.

15. Context Filter

• Acts as a master filter before others.

Practical: Context filter for "Asia" before other filters.

16. Conditional Filter

• Filters based on a condition.

Practical: Keep rows where Profit > 1000.

17. Slicing Filter

• Narrow down by a measure’s range.

Practical: Slice Sales between 200–1000.

18. Grouping

• Combine similar values.

Practical: Group "Chairs" & "Tables" into "Furniture Items".

19. Hierarchy

• Drill-down from higher to lower levels.

Practical: Region → Country → State hierarchy.

20. Sort Data

• Ascending or descending order by field.

Practical: Sort products by highest profit.

:: Best of Luck ::