> For the complete documentation index, see [llms.txt](https://presight.gitbook.io/presight-v1.0/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://presight.gitbook.io/presight-v1.0/model/formulas/data-transformation-formulas.md). # 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. *** {% hint style="success" %} In the doc below, anytime you come across the keyword `column`, understand that this applies to both data source columns and custom columns. {% endhint %} ## **Function Categories** For better understanding, we've grouped the supported functions into the following categories: 1. [**Basic Aggregation Functions**](#id-1.-basic-aggregation-functions) 2. [**Group Aggregation Functions**](#id-2.-group-aggregation-functions) 3. [**Mathematical Functions**](#id-3.-mathematical-functions) 4. [**Conditional Aggregation Functions**](#id-4.-conditional-aggregation-functions) 5. [**Ranking and Window Functions**](#id-5.-ranking-and-window-functions) 6. [**Value Navigation Functions**](#id-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. ```plaintext SUM(Revenue) ``` * **COUNT** * **Description:** Returns the count of all values in the given column. * **Syntax:** `COUNT(column)` * **Example:** Count the total number of transactions. ```plaintext 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. ```plaintext COUNTD(Customer ID) ``` * **MIN** * **Description:** Returns the smallest value in the given column. * **Syntax:** `MIN(column)` * **Example:** Find the lowest sale price. ```plaintext MIN(Sale Price) ``` * **MAX** * **Description:** Returns the largest value in the given column. * **Syntax:** `MAX(column)` * **Example:** Identify the highest order quantity. ```plaintext MAX(Order Quantity) ``` * **AVG** * **Description:** Returns the average value of the given column. * **Syntax:** `AVG(column)` * **Example:** Calculate the average customer rating. ```plaintext 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. ```plaintext 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. ```plaintext 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. ```plaintext 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. ```plaintext 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. ```plaintext 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. ```plaintext 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. ```plaintext 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. ```plaintext 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. ```plaintext 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. ```plaintext 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. ```plaintext 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. ```plaintext 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. ```plaintext 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. ```plaintext 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". ```plaintext 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. ```plaintext 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. ```plaintext 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. ```plaintext 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%. ```plaintext 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. ```plaintext 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. ```plaintext 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. ```plaintext 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. ```plaintext 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. ```plaintext 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. ```plaintext 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. ```plaintext 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 ****.