DEF_SUB calculates the value of the aggregate indicator based on the dimension in the analysis area with ignorance of the dimension specified by the function. DEF_SUB involves the dimension in the analysis area, so adding or deleting the dimension affects the function result.
Compared to DEF_ADD, which involves the dimension specified by the function and in the analysis area, DEF_SUB ignores the dimension in the analysis area.
DEF_SUB(Aggregate indicator,[Dimension1,Dimension2,...],[Filter condition1,Filter condition2,...])
Perform calculations on indicator fields that meet the filter conditions based on the dimension in the analysis area with ignorance of the dimension specified by the function.
Aggregate indicator
1. There must be one and only aggregate indicator in the function.
2. The aggregate indicator supports nesting (output by any function), including DEF functions.
Specified dimension
1. Dimension specified by the function
2. 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_SUB(Aggregate indicator,[],[Filter condition 1,Filter condition 2,...]).
Drag Region and Category Description into the analysis area, as shown in the following figure.
Since DEF_SUB performs calculations based on the dimension in the analysis area with ignorance of the dimension specified by the function, Region and Category Description are ignored in the calculation.
Calculate the sales of each region.
DEF_SUB(SUM_AGG(Sales),[Category Description])
There are two dimensions in the analysis area, but only the dimension Region is needed in the calculation, so the field Category Description needs to be ignored.
Specified dimension: Category Description
Analysis area dimension: Region and Category Description
Function dimension: Region and Category Description - Category Description = Region
Aggregate indicator: SUM_AGG(Sales)
Calculate the total sales of each region.
This section introduces how to calculate the average quarter sales in 2017 and compare them with the quarter sales of each region.
Formula: Average quarter sales of each region=Total quarter sales/Number of regions
1. Create an analysis subject and upload the built-in sample data table ENdemo_Store_Sales_Statistics, as shown in the following figure.
2. Create a component to display the quarter sales of each region. Set the chart type as Custom Chart and set the grouping method of Sale Date to Year Quarter, as shown in the following figure.
3. Add a calculation indicator to calculate the average quarter sales of each region, as shown in the following figure.
Drag Sale Date and Region into the analysis area.
Quarter sales
DEF(SUM_AGG(Sales),[Region])
Analysis area dimension: Sale Date (grouping method: Year Quarter) and Region
Specified dimension: Region
Sum up the sales in each quarter.
Number of regions
DEF(COUNTD_AGG(Region))
The dimension in the analysis area does not affect the calculation of DEF.
Specified dimension: /
Calculate the number of regions in the sample table.
Average quarter sales of each region
DEF_SUB(SUM_AGG(Sales),[Region])/DEF(COUNTD_AGG(Region))
Total sales in each quarter / Number of regions
4. Drag the field Average Quarter Sales of each Region into the Vertical Axis of the analysis area and set its display form to Line in Graphic Property, as shown in the following figure.
滑鼠選中內容，快速回饋問題
滑鼠選中存在疑惑的內容，即可快速回饋問題，我們將會跟進處理。
不再提示
10s後關閉
Submitted successfully
Network busy