Blog

You are currently viewing Aggregate and Type Conversion Function in Tableau

Aggregate and Type Conversion Function in Tableau

Loading

Aggregate and type conversion function in Tableau are crucial to making data analysis smooth and effective. Aggregation helps transform large datasets into meaningful insights, such as adding up values ​​or calculating averages. Meanwhile, type conversion ensures that data is in the right format for calculations, such as converting text to numbers for analysis. These functions are useful for you to streamline analysis, especially when you are working with diverse datasets.

When to use Aggregate and type conversion function in Tableau

Aggregate functions

Use them when it’s important to summarize data, such as calculating total sales or average profit.

Example: For Analyzing daily sales we can use SUM() to show total monthly sales.

Type conversion functions

These come in handy when converting data formats for calculations or display.

Example: If want store date as text we can use DATE() to convert it for time-based analysis.

Common Aggregate Functions

SUM()

Adds numeric values.

Example: SUM(Sales) returns total sales for a region or month.

AVG()

Averages numeric values.

Example: AVG(Profit) returns the average profit.

COUNT()

Counts the non-null values ​​in a field.

Example: COUNT(Customer ID) counts the total number of customers.

COUNTD()

Counts unique values.

Example: COUNTD(Product ID) returns the total number of unique products.

MIN() and MAX()

Finds the lowest and highest value.

Example: MIN(Order Date) returns the earliest order date.

MEDIAN()

Returns the median value of a dataset.

Example: MEDIAN(Sales) returns the median sales value.

STDEV() and VARIANCE()

Measures data spread or variability.

Example: STDEV(sales) calculates the sales standard deviation.

Common Type Conversion Functions

INT()

Converts to integer.

Example: INT(‘100’) converts the string ‘100’ to the number 100.

FLOAT()

Converts to decimal number.

Example: FLOAT(‘123.45’) converts the string ‘123.45’ to a decimal number.

STRING()

Converts numbers/dates to text.

Example: STR(123) converts the number 123 to text.

DATE()

Converts text or numbers to dates.

Example: DATE(‘2024-10-05’) converts string to proper date.

DATETIME()

Converts text/numbers to date and time.

Example: DATETIME(‘2024-10-05 14:30:00’) returns a timestamp.

Why these functions are important

Summarizing and analyzing data

Aggregate functions help transform large datasets into valuable information. Type conversion ensures that the data is in the correct format, making analysis easier and free from errors.

Improve data quality

Both functions ensure consistency – aggregates simplify data, and type conversion guarantees the correct format for calculations, improving the quality of your analysis.

Flexibility and compatibility

These functions allow you to handle different data formats, making them a must when working with multiple sources.

Better visualization

Aggregates like SUM() or AVG() provide key metrics for dashboards. Type conversions ensure that data is displayed correctly, improving your visual storytelling.

Steps to perform several aggregate and type conversion functions

Step 1: Open Tableau

Step 2: Click on Text file to connect with Tableau

Step 3: Browse csv file (or other file format as your need) and click on Open

Step 4: Data has loaded and now click on Sheet 1 (Worksheet) for further process

SUM Function (agg.)

Step 5: Click on Analysis tab and select Create Calculated Field…

Step 6: Specify a name (Total Amount) for field and fill the following:

Output for SUM Function (agg.): Drag Total Amount (from data pane) and drop over Text (Marks card)

Note:

  • It returned 90,660 in Total Amount along with table.

AVG Function (agg.)

Step 8: Click on Analysis tab and select Create Calculated Field…

Step 9: Specify a name (Average Age of Customer) for field and fill the following:

Output for AVG Function (agg.): Drag Average Age of Customer (from data pane) and drop over Text (Marks card)

Note:

  • It returned 41.15 in Average Age of Customers along with table.

COUNT Function (agg.)

Step 11: Click on Analysis tab and select Create Calculated Field…

Step 12: Specify a name (Total Entries) for field and fill the following:

Output for COUNT Function (agg.): Drag Total Entries (from data pane) and drop over Text (Marks card)

Note:

  • It returned 137 rows in Total Entries along with table.

COUNTD Function (agg.)

Step 14: Click on Analysis tab and select Create Calculated Field…

Step 15: Specify a name (Total Salesperson) for field and fill the following:

Output for COUNTD Function (agg.): Drag Total Salesperon (from data pane) and drop over Text (Marks card)

Note:

  • It returned 6 in Total Salesperson along with table.

STR Function (Type Conversion)

Step 17: Click on Analysis tab and select Create Calculated Field…

Step 18: Specify a name (Date to String) for field and fill the following:

Output for STR Function (Type Conversion): Drag Date to String (from data pane) and drop over Text (Marks card)

Note:

  • It returned all stored dates as String in Date to String along with table.

DATE Function (Type Conversion)

Step 20: Click on Analysis tab and select Create Calculated Field…

Step 21: Specify a name (String to Date Only) for field and fill the following:

Output for DATE Function (Type Conversion): Go back to Data Source and see the last column

Note:

  • It returned Date Only in ‘String to Date Only’ field along with table.

Conclusion

Aggregate and type conversion functions in Tableau are essential for efficiently summarizing, transforming, and presenting data. Whether you’re collapsing huge datasets or converting data formats, these functions simplify the entire process, allowing you to focus on extracting information.

If you like the article and would like to support me, make sure to:

Leave a Reply