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.
DEF(Aggregate indicator,[Dimension1,Dimension2,...],[Filter condition1,Filter condition2,...])
Perform calculations on indicator fields that meet the filter conditions based on the specified dimension.
Aggregate indicator
1. There must be one and only aggregate indicator in the function.
2. The aggregate indicator supports nesting (output by any function).
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.
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,...]).
The following table introduces the usage and role of the DEF function.
DEF(COUNTD_AGG(Order ID),[ID Number])
Specified dimension: ID Number
Aggregate indicator: COUNTD_AGG(Order ID)
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))
Specified dimension: /
Aggregate calculation: SUM_AGG(Sales)
With no specified dimension, this function calculates the total sales.
Sales by Countries
DEF(SUM_AGG(Sales),[Country])
Specified dimension: Country
This function calculates the sales of each country.
Sales of different brands by countries
DEF(SUM_AGG(Sales),[Country,Brand])
Specified dimension: Country and Brand
This function calculates the sales of different brands of each country.
Sales by countries in 2013
DEF(SUM_AGG(Sales),[Country],[Year=2013])
Filter condition: 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.
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.
Sample data: Auto Industry Sales Funnel.xlsx
Create a subject and upload the sample data, as shown in the following figure.
Add a calculation indicator and use the DEF function, as shown in the following figure.
Number of customers by node
DEF(SUM_AGG(Number of Customers),[Key Node])
Specified dimension: Key Node
Aggregate calculation: SUM_AGG(Number of Customers)
Calculate the total number of customers on each node.
Number of inbound customers
DEF(SUM_AGG(Number of Customers),[],[Key Node="active inbound"])
Filter condition: Key Node="active inbound"
Calculate the number of inbound customers (on the first node).
Conversion rate = Number of customers on each node / Number of inbound customers, as shown in the following figure.
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.
The following documents introduce how to use the DEF function properly in various business scenarios.
Cohort Analysis
Cross-Row Calculation of Growth Rate
Detailed Analysis of Goal Completion
Consumption Cycle
Monthly Profit Day Statistics
YOY Ratio of Accumulated Sales as of Each Month
Analysis of Sales Gap
Comparison of Operating Conditions in Different Regions
滑鼠選中內容，快速回饋問題
滑鼠選中存在疑惑的內容，即可快速回饋問題，我們將會跟進處理。
不再提示
10s後關閉
Submitted successfully
Network busy