Overview
The DEF function calculates the value of the aggregate indicator based on the specified dimension.
The dimension of the analysis area will not be referenced in the DEF function, so adding or deleting the dimension does not affect the function result.
| Grammar | DEF(Aggregate indicator,[Dimension1,Dimension2,...],[Filter condition1,Filter condition2,...]) | Perform calculations on indicator fields that meet the filter conditions based on the specified dimension. |
|---|---|---|
| Parameter 1 | Aggregate indicator | 1. There must be one and only aggregate indicator in the function. 2. The aggregate indicator supports nesting (output by any function).  Note: |
| Parameter 2 | Specified dimension | 1. Dimension based on which the calculation is performed 2. The dimension parameter can be empty, if so, the function performs the aggregate calculation on the whole table. 3. If there is only one dimension parameter, the symbol [ ] can be omitted. |
| Parameter 3 | Filter condition | 1. The filter condition can be empty, if so, the function performs the aggregate calculation on all fields of the indicator. 2. The filter condition supports setting filter details and nesting indicators (output by DEF functions), but does not support aggregate functions. 3. Multiple filter conditions are supported, and those of the same level are performed based on the AND relationship. 4. If there is only one filter condition, the symbol [ ] can be omitted. 5. If the specified dimension is empty, the function format is DEF(Aggregate indicator,[],[Filter condition 1,Filter condition 2,...]). |
Examples
The following table introduces the usage and role of the DEF function.
| Scenario | Formula | Description |
|---|---|---|
| Number of orders by each customer | DEF(COUNTD_AGG(Order ID),[ID Number]) |
This function performs a distinct count on the number of order IDs made by each ID number. |
Total sales of the platform | DEF(SUM_AGG(Sales)) |
With no specified dimension, this function calculates the total sales. |
Sales by Countries | DEF(SUM_AGG(Sales),[Country]) |
This function calculates the sales of each country. |
Sales of different brands by countries | DEF(SUM_AGG(Sales),[Country,Brand]) |
This function calculates the sales of different brands of each country. |
Sales by countries in 2013 | DEF(SUM_AGG(Sales),[Country],[Year=2013]) |
This function calculates the sales of each country in 2013. |
Drag the above four indicators into the analysis area, as shown in the following figure.
Conversion Rate Calculation
The following section takes the sales of the auto industry as an example to introduce the actual practices of the DEF function.
In the purchase cycle of the auto industry, there are several key nodes — active inbound, store visiting, test drive, quotation, order placing, and delivery. To calculate the conversion rate from the first node (active inbound) to each node, use the formula Conversion rate = Number of customers on each node / Number of inbound customers.
Adding Sample Data
Sample data: Auto Industry Sales Funnel.xlsx
Create a subject and upload the sample data, as shown in the following figure.
Calculating the Number of Customers by Node
Add a calculation indicator and use the DEF function, as shown in the following figure.
| Scenario | Formula | Description |
|---|---|---|
Number of customers by node | DEF(SUM_AGG(Number of Customers),[Key Node]) |
Calculate the total number of customers on each node. |
Calculating the Number of Inbound Customers
Add a calculation indicator and use the DEF function, as shown in the following figure.
| Scenario | Formula | Description |
|---|---|---|
Number of inbound customers | DEF(SUM_AGG(Number of Customers),[],[Key Node="active inbound"]) |
Calculate the number of inbound customers (on the first node). |
Calculating the Conversion Rate
Conversion rate = Number of customers on each node / Number of inbound customers, as shown in the following figure.
Creating a Component
Drag related fields into the analysis area and change the value format of Conversion Rate to Percentage, as shown in the following figure.
As you can see from the component, only 0.27% of inbound customers complete the delivery.
Business Scenarios
The following documents introduce how to use the DEF function properly in various business scenarios.
| Business Scenarios |
|---|
| Analysis of Purchase Frequency |
Previous：Analysis Function Overview
