In-built Aggregate Functions
Analytics Plus offers a wide range of powerful in-built aggregate functions to create powerful metrics and address your business needs.
Aggregate Functions
| Function | Description |
| Sum - sum(numeric_column) | Returns the sum of the 'numeric_column' |
| Avg - avg(numeric_column) | Returns the average of the 'numeric_column' |
| Min - Min(numberic_column) | Returns the minimum of the 'numeric column' |
| Max - max(numeric_column) | Returns the maximum of the 'numeric_column' |
| Count - count(column) | Returns the number of rows (values) in the given column |
| Stddev - stddev(numeric_column) | Returns the standard deviation of the 'numeric_column' |
| Variance - variance(numeric_column) | Returns the variance of the 'numeric_column' |
| Distinct Count - count(distinct(column)) | Returns the distinct number of values (rows) in the given column. |
| SumIf - sumif(condition, expr1, expr2) | Returns the sum of expr1, if condition is true. Else it will return the sum of expr 2. The expr1 and the expr2 can be an expression or just a numeric column. The expr2 is an optional argument. Example: sumif(Region = 'East, Sales, 0) will return the sum of values in the sales column for all the records which matches the value East in the Region column. Else it will return 0. |
| AvgIf - avgif(condition, expr1, expr2) | Returns the average of expr1, if condition is true. Else it will return the average of expr 2. The expr1 and the expr2 can be an expression or just a numeric column. The expr2 is an optional argument. Example: avgif(Region = 'East, Sales, 0) will return the average of values in the sales column for all the records which matches the value East in the Region column. Else it will return 0. |
| CountIf - countif(condition) | Returns the number of rows (records) that satisfies the condition. Example: countif(Region='East') will return the number of rows (records) in the table which matches the value East in the Region column. |
| YTD - YTD(AggExpr,Date_Col) | Year-to-dateis a period, starting from the beginning of the current year and ends at the current day.
Arguments: Example: YTD(Sum(sales), OrderDate) will return the Sum of sales for the current Year till current day, based on date column 'OrderDat |
| QTD - QTD(AggExpr,Date_Col) | Quarter-to-dateis a period, starting from the beginning of the current quarter and ends at the current day.
Arguments: Example: QTD(Sum(sales), OrderDate) will return the Sum of sales for the current Quarter till current day, based on date column 'OrderDate'. |
| MTD - MTD(AggExpr,Date_Col) | Month-to-dateis a period, starting from the beginning of the current month and ends at the current day.
Arguments: Example: MTD(Sum(sales), OrderDate) will return the Sum of sales for the current Month till current day, based on date column 'OrderDate'. |
Groupby Shifting Expressions
The Groupby Shifting Expressions in Analytics Plus lets you specify the grouping for column data as required. You can easily gain control over how the columns are grouped for computation instead of the default aggregate formula value calculation computed for each data or group in a report. The following are the supported expressions.
Include Groupby
This function allows you to add one more column to the existing grouping or dimension for computation. For example, let's calculate the Average Sales per Customer by adding the Customer Name column to the existing group Sum of Sales.
Format: AggExpr(include_groupby(AggExpr, Column-to-Include))
Example function: avg(include_groupby(sum("StoreSales"."Sales"),"StoreSales"."Customer Name"))
Exclude Groupby
This function omits or ignores a particular column value when computing aggregate values irrespective of the dimension or grouping used in the report.
Format: exclude_groupby(AggExpr, Column-to-Exclude)
Example: The below report shows the Sum of sales values grouped based on each Region for each Product Category. Let's exclude the Region column and calculate the Total sales for each Product Category regardless of the existing grouping applied in the report using the following function: exclude_groupby(sum("StoreSales"."Sales"),"StoreSales"."Region")
Fixed Groupby
This function enables you to group specific column values to compute values independent of the grouping available in the report.
Format: AggExpr(fixed_groupby(AggExpr, Fixed-Column-to-compute))
Example function: avg(fixed_groupby(sum("StoreSales"."Sales"),absquarter("StoreSales"."Date")))
The above example calculates the absolute quarter of the Date column ignoring the grouping applied in the report.
Map Groupby
This function allows you to perform computation based on a specified column rather than the column used in the report.
Format: map_groupby(AggExpr, Column in Report, Column to Map)
Example: map_groupby(sum("StoreSales"."Delivered Sales"),"StoreSales"."Order Date", "StoreSales"."Delivery Date")
The above example returns the Sum of sales (Delivered Sales) amount for delivered orders based on Delivery date. The Total sales in the report below gives the sales amount for all orders received based on Order Date.
Ignore Filters
This function lets you ignore the filters applied on the report based on a specified column.
Format: ignore_filters(AggExpr, Column-to-Ignore, Filter Type).
You can specify any one of the filter types. Please note that the filter type is optional.
- 0 - Ignores the User Filters applied over the report.
- 1 - Ignores the Filters applied over the report.
- 2 - Ignores both the User Filters and the Filters applied over the report.
If the Filter Type is not specified, 0 will be taken as the default value and ignores the User Filters applied over the report.
Example: ignore_filters(sum("StoreSales"."Sales"), "StoreSales"."Region")
The above example returns the Total sales ignoring the User Filter (as the filter type is not specified) based on the Region column.
Example: ignore_filters(sum("StoreSales"."Sales"), "StoreSales"."Region", 2)
The above example returns the Total sales ignoring the User Filters (East Region) and Filters (Central and East Region) as the filter type is specified as 2.
