L.19a_-_Calculations

Agenda

• Topics covered include:

  • Creating Tables: Techniques to organize data effectively.

  • Using Formulas & Calculations: Implementing mathematical operations in data analysis.

Formulas in Tableau

• Introduces the concept of using formulas within Tableau for data operations, which enhances the analytical capability and data manipulation.

Topics to Cover

• Introduction: An overview of effectively utilizing Tableau formulas for analytical tasks.

• Constants and Operators: Emphasizes the importance of recalling data types (numeric, string, date, boolean) and operators for data manipulation.

• Variables: Discusses the significance of understanding fields and parameters in dynamic calculations.

• Branching: Utilizes conditional functions such as IIF and IF-THEN-ELSE for logic implementation in analyses.

• Common Functions: Includes explanations of commonly used functions in Tableau: Date, Text, and Math functions, which are pivotal for data processing.

Introduction to Tableau Formula Language

• Highlights similarities to Excel's formula language, making the transition easier for users familiar with Excel.

• Key aspects include:

  • Designation of data types for constants to ensure accurate calculations.

  • Usage of fields and parameters to streamline analytical processes.

  • Understanding syntax and aggregate functions that facilitate complex data analysis.

  • Familiarization with common functions within Tableau to improve efficiency.

Quick Table Calculations

• Methods for Quick Calculations include:

  • Quick Table Calculation: Built-in calculations with preset options that simplify data analysis.

  • Table Calculation Dialog Box: Allows users to select and configure various calculation types easily.

  • Custom Table Calculation: Opportunity to construct complex calculations using table calculation syntax.

• Uses: These calculations assist with tasks such as:

  • Ranking values for comparative analysis.

  • Running totals to track cumulative data changes.

  • Percent of total calculations to assess individual contributions to overall totals.

Characteristics of Quick Table Calculations

• These calculations are executed after data retrieval and are based on the current view data, emphasizing their relevance.

• Filters applied to results can refine data perspectives based on user specifications.

• Manual creation is possible, and there are pre-built options available for user convenience.

• Users have the ability to save calculations for future use, promoting efficiency in ongoing analysis.

Common Calculation Types in Tableau

• Running Total: Provides cumulative totals across data sets (e.g., quarterly sales tracking).

• Difference: Measures variance from previous values (e.g., semester enrollment changes).

• Percent Difference: Expresses change in percentage from prior values (e.g., monthly sales comparisons).

• Percent of Total: Represents proportions relative to the entire data set (e.g., monthly sales percentages).

• Rank: Ranks values within a dataset to identify higher and lower performing elements (e.g., monthly sales ranking).

• Percentile: Ranks data statistically (e.g., evaluation of test scores).

• Moving Average: Smooths data fluctuations to highlight trends (e.g., sales trends analysis).

• YTD Total: Tracks year-to-date cumulative figures.

• YTD Growth: Analyzes percentage change compared to the prior year.

• Compound Growth Rate: Provides insights through percentage changes from the initial value.

• Year Over Year Growth: Calculates yearly comparative percentage growth.

Importance of Table Calculations

• Table calculations are significant in the realm of data analysis as they allow users to modify the presentation of data without altering the underlying source data, preserving data integrity and analysis reliability.

Computing Table Calculations

• Compute Using: The default method for data calculations relies on the visual arrangement of data.

• The order in which visual elements are arranged can greatly affect calculations (e.g., running totals based on the column order). Users have flexibility beyond default calculation behaviors based on their analytical needs.

Creating Calculated Fields

• There is a constant need for calculated fields in Tableau to facilitate deeper analysis.

• These fields can be viewed conceptually as new columns added to datasets, enriching the data with context and valuable insights necessary for decision-making.

Constants in Tableau

• Constants include numbers, strings, Booleans, or dates, each requiring proper designation to maintain accuracy:

  • String: Represents textual data with the notation "string".

  • Number: Numeric values are handled as 12345.

  • Date: Dates need to be formatted as #1/1/2021#.

  • Boolean: Logical values indicated as TRUE or FALSE.

Importance of Data Types

• The consistency of operations is directly influenced by data type consistency. An example of a valid operation is #6/1/2021# - #3/1/2021#, whereas an invalid example would be #6/1/2021# - "3/1/2021".

• Each field must observe its designated data type to ensure operational accuracy.

Changing Data Types

• For successful operations, it is sometimes necessary to convert fields to compatible types. Users can employ type conversion functions contained within the formula editor to facilitate this.

Consistent Data Types

• To ensure functional operations return accurate and expected results, it is vital to maintain identical data types across operations. Examples include valid date operations that reliably yield consistent results (such as calculating the days between specific dates).

Operations on Numeric Data

• Valid operations can be performed on any numeric fields, often necessitating type conversions when dealing with mixed data types. Such versatility can yield identical numeric results despite varying input formats.

Mathematical Operators

• Operators applicable to various field types, such as:

  • Addition, Subtraction, Multiplication, Division: These can be utilized across Number and Date fields.

• Remember the order of operations (PEMDAS) to maintain accuracy in calculations.

Comparison Operators

• Used to compare numeric, date, and string fields, yielding Boolean results (TRUE/FALSE). Common examples include:

  • Equality: = , Inequality: != or <>

  • Other comparison operators include: >, <, with specific examples provided to illustrate operator function in practice.

String Handling

• It is important to note that strings cannot be added but can be concatenated; for example, "abc" + "def" results in "abcdef".

Fields and Parameters

• Emphasizes the critical importance of leveraging fields and parameters instead of relying solely on constants:

  • Enhances flexibility, allowing for seamless data adjustments and analyses.

  • Improves auditability and adaptability for new analysts, facilitating onboarding and collaborative analysis.

Differences Between Fields and Parameters

• Fields: Result from data sources or calculated metrics, immutable by user actions once established.

• Parameters: Allow for user-modifiable values that can be dynamically incorporated into visualizations, adaptable to various analytical needs.

Syntax for Fields and Parameters

• Correct syntax when referencing fields and parameters is crucial to ensure data integrity and calculation accuracy.