Data Transformation Formulas

Presight's Table Builder and Fomula Breakdown not only provide an intuitive, Excel-like interface for data manipulation but also support a wide range of advanced formulas. These formulas empower you to perform complex calculations, aggregations, and data transformations directly within your tables, without the need for SQL or programming knowledge. This documentation outlines the detailed functions supported by Table Builder, enabling you to harness the full potential of your data analysis.

In the doc below, anytime you come across the keyword column, understand that this applies to both data source columns and custom columns.

Function Categories

For better understanding, we've grouped the supported functions into the following categories:

  1. Basic Aggregation Functions

  2. Group Aggregation Functions

  3. Mathematical Functions

  4. Conditional Aggregation Functions

  5. Ranking and Window Functions

  6. Value Navigation Functions

1. Basic Aggregation Functions

These functions perform calculations across all rows in a column.

Functions:

  • SUM

    • Description: Returns the sum of all values in the given column.

    • Syntax: SUM(column)

    • Example:

      Calculate total revenue.

      SUM(Revenue)

  • COUNT

    • Description: Returns the count of all values in the given column.

    • Syntax: COUNT(column)

    • Example:

      Count the total number of transactions.

      COUNT(Transaction ID)

  • COUNTD

    • Description: Returns the count of unique values in the given column.

    • Syntax: COUNTD(column)

    • Example:

      Count the number of unique customers.

      COUNTD(Customer ID)

  • MIN

    • Description: Returns the smallest value in the given column.

    • Syntax: MIN(column)

    • Example:

      Find the lowest sale price.

      MIN(Sale Price)

  • MAX

    • Description: Returns the largest value in the given column.

    • Syntax: MAX(column)

    • Example:

      Identify the highest order quantity.

      MAX(Order Quantity)

  • AVG

    • Description: Returns the average value of the given column.

    • Syntax: AVG(column)

    • Example:

      Calculate the average customer rating.

      AVG(Customer Rating)

  • FIRST

    • Description: Returns the first value in the given column based on data order.

    • Syntax: FIRST(column)

    • Example:

      Get the first entry date in the dataset.

      FIRST(Entry Date)

  • LAST

    • Description: Returns the last value in the given column based on data order.

    • Syntax: LAST(column)

    • Example:

      Find the most recent update timestamp.

      LAST(Update Timestamp)

2. Group Aggregation Functions

Group aggregation functions perform calculations across groups of rows defined by specific criteria, similar to "GROUP BY" operations in SQL.

Functions:

  • GROUP_SUM

    • Description: Returns the sum of values for each group.

    • Syntax: GROUP_SUM(column,time_column,group_by column)

    • Parameters:

      • Dimension : The target dimension to aggregate.

      • Time Column: The time dimension based on which the dimension shall be aggregated over.

      • Group-by Column [multiple] (optional): A set of dimensions at the granularity in which the aggregate result will be plotted into.

    • Example:

      Calculate the total sales for each region by each order date.

      GROUP_SUM(Sales, Order Date, Region)

  • GROUP_AVG

    • Description: Returns the average value for each group.

    • Syntax: GROUP_AVG(column,time_column,group_by column)

    • Example:

      Calculate the average order value per customer.

      GROUP_AVG(Order Value, Order Date, Customer ID)

  • GROUP_COUNT

    • Description: Returns the number of values for each group.

    • Syntax: GROUP_COUNT(column,time_column,group_by column)

    • Example:

      Count the number of orders per region.

      GROUP_COUNT(Order ID, Order Date, Region)

  • GROUP_COUNTD

    • Description: Returns the count of unique values for each group.

    • Syntax: GROUP_COUNTD(column,time_column,group_by column)

    • Example:

      Count the number of unique customers per sales representative.

      GROUP_COUNTD(Customer ID, Order Date, Sales Person)

  • GROUP_MIN

    • Description: Returns the smallest value in each group.

    • Syntax: GROUP_MIN(column,time_column,group_by column)

    • Example:

      Find the smallest transaction amount per day.

      GROUP_MIN(Transaction Amount)

  • GROUP_MAX

    • Description: Returns the largest value in each group.

    • Syntax: GROUP_MAX(column,time_column,group_by column)

    • Example:

      Determine the highest sales figure per quarter.

      GROUP_MAX(Sales Amount)

  • GROUP_FIRST

    • Description: Returns the first value in each group based on the data order.

    • Syntax: GROUP_FIRST(column,time_column,group_by column)

    • Example:

      Get the first order date for each customer.

      GROUP_FIRST(Order Date)

  • GROUP_LAST

    • Description: Returns the last value in each group based on the data order.

    • Syntax: GROUP_LAST(column,time_column,group_by column)

    • Example:

      Find the most recent activity date per user.

      GROUP_LAST(Activity Date)

3. Mathematical Functions

Mathematical functions perform basic arithmetic operations and number manipulations.

Functions:

  • ABS

    • Description: Returns the absolute (positive) value of a number.

    • Syntax: ABS(number)

    • Example:

      Ensure all profit/loss values are positive.

      ABS(Profit)

  • ROUND

    • Description: Rounds a number to the nearest integer.

    • Syntax: ROUND(number, [decimal_places])

    • Parameters:

      • decimal_places (optional): Number of decimal places to round to.

    • Example:

      Round the sales amount to two decimal places.

      ROUND(Sales Amount, 2)

  • CEIL

    • Description: Returns the smallest integer greater than or equal to a number.

    • Syntax: CEIL(number)

    • Example:

      Calculate the minimum number of containers needed when each holds 50 items.

      CEIL(Items / 50)

  • FLOOR

    • Description: Returns the largest integer less than or equal to a number.

    • Syntax: FLOOR(number)

    • Example:

      Determine the number of full hours from total minutes.

      FLOOR(Total Minutes / 60)

4. Conditional Aggregation Functions

Conditional aggregation functions perform calculations on a subset of data that meets specified conditions.

Functions:

  • SUMIF

    • Description: Returns the sum of values that meet a condition.

    • Syntax: SUMIF(expression, condition)

    • Example:

      Sum sales where the region is "East".

      SUMIF(Sales Amount, Region = "East")

  • COUNTIF

    • Description: Returns the count of values that meet a condition.

    • Syntax: COUNTIF(expression, condition)

    • Example:

      Count orders over $1,000.

      COUNTIF(Order Amount, Order Amount > 1000)

  • COUNTDIF

    • Description: Returns the count of unique values that meet a condition.

    • Syntax: COUNTDIF(expression, condition)

    • Example:

      Count unique customers who purchased Product X.

      COUNTDIF(Customer ID, Product = "Product X")

  • MINIF

    • Description: Returns the smallest value that meets a condition.

    • Syntax: MINIF(expression, condition)

    • Example:

      Find the minimum sale price in Category A.

      MINIF(Sale Price, Category = "A")

  • MAXIF

    • Description: Returns the largest value that meets a condition.

    • Syntax: MAXIF(expression, condition)

    • Example:

      Identify the highest score among students with attendance over 90%.

      MAXIF(Score, Attendance > 90)

  • AVGIF

    • Description: Returns the average of values that meet a condition.

    • Syntax: AVGIF(expression, condition)

    • Example:

      Calculate average order value for VIP customers.

      AVGIF(Order Value, Customer Tier = "VIP")

5. Ranking and Window Functions

These functions provide capabilities for ranking and performing calculations across a set of table rows related to the current row.

Functions:

  • RANK

    • Description: Assigns a rank to each row within a partition of a result set.

    • Syntax: RANK(expression)

    • Example:

      Rank products based on sales volume.

      RANK(SUM(Sales Volume))

  • FIRST_VALUE

    • Description: Returns the first value in an ordered set of values.

    • Syntax: FIRST_VALUE(expression)

    • Example:

      Get the first purchase date per customer.

      FIRST_VALUE(Purchase Date)

  • LAST_VALUE

    • Description: Returns the last value in an ordered set of values.

    • Syntax: LAST_VALUE(expression)

    • Example:

      Find the most recent login time per user.

      LAST_VALUE(Login Time)

6. Value Navigation Functions

These functions help navigate through ordered data to access previous or next values relative to the current row.

Functions:

  • PREVIOUS

    • Description: Returns the previous value in an ordered set.

    • Syntax: PREVIOUS(expression)

    • Example:

      Compare current month's sales with the previous month's.

      PREVIOUS(Monthly Sales)

  • PREVIOUS_NTH

    • Description: Returns the previous nth value in an ordered set.

    • Syntax: PREVIOUS_NTH(expression, n)

    • Parameters:

      • n: The number of steps back to retrieve the value.

    • Example:

      Get the sales figure from two periods ago.

      PREVIOUS_NTH(Monthly Sales, 2)

  • NEXT

    • Description: Returns the next value in an ordered set.

    • Syntax: NEXT(expression)

    • Example:

      Forecast future inventory needs based on the next month's planned sales.

      NEXT(Planned Sales)

Usage Notes

  • Expressions: In all functions, expression refers to the column or calculated field you want to operate on.

  • Conditions: For conditional functions like SUMIF, the condition should be a logical expression that returns true or false.

  • Data Order: Functions like FIRST, LAST, PREVIOUS, and NEXT depend on the data's order. Ensure your data is appropriately sorted to get accurate results.

  • Grouping: When using group functions, make sure your data is grouped correctly based on the desired grouping columns.

Conclusion

The advanced formulas supported in Presight's Table Builder equip you with robust tools to perform intricate data transformations and analyses. By leveraging these functions, you can:

  • Conduct complex calculations without writing SQL.

  • Enhance your datasets with new insights.

  • Create customized metrics tailored to your business needs.

  • Streamline your data analysis workflow within a single platform.

We encourage you to experiment with these functions to unlock deeper insights and make data-driven decisions more effectively.

Need Assistance?

If you have questions or need help using these functions:

  • In-App Support: Click the Support option in the bottom left corner of your Presight workspace.

  • Email Us: Reach out to our support team at support@presight.io.

PreviousMetric FormulasNextThe Data Docs

Last updated