Overview
CLEAN targets fields to ignore the filtering effect of these fields during the current DEF calculation.
Syntax | CLEAN(Field 1,Field 2,Field 3....) /CLEAN("ALL") | Function used to ignore the filtering effects generated by the specified fields during the calculation of the current analysis function |
Parameter | Field 1,Field 2,Field 3.... | Fields that generate the filtering effects to be ignored: 1. Dimension/indicator fields of any types (including calculation fields) are supported. 2. If "ALL" is specified in CLEAN, the system clears the filtering effects generated by all fields during the calculation of the current analysis function. 3. Default field values are not supported. |
Notes
This function can only be used in DEF/DEF_ADD/DEF_SUN as a standalone filtering condition. For example, DEF(SUM_AGG(Indicator),[Province,City],[CLEAN_WIDGET(City)])
Calculation Logic
Calculation Range
1. CLEAN(Field 1,Field 2,Field 3....)
This function is used to remove the filtering effects generated by fields. The following table shows the effect.
Range | Filter Condition | Calculation Field in Component | Result |
Component | The field City is added to the result filter. Filter 1: The value of the field City is not equal to Wuxi. Filter 2: The value of the field Sales Volume is greater than 100. | DEF(SUM_AGG(Sales Volume),[City],[CLEAN(City)]) | All filtering effects of the field City are ignored. |
Dashboard | The value of the field City is Suzhou, and Changzhou by the text drop-down filter. |
2. CLEAN("ALL")
The calculated results from the current analysis function are not subject to any filtering effects (but subject to those out of the dashboard and component, such as permission filtering and scheduled filtering).
3. Summary
The following table shows the function range.
Range | Field Filtering | CLEAN(Field 1,Field 2,Field 3....) | CLEAN("ALL") |
Component | Result Filter Drilling | Field filtering effects are ignored. | The filtering effects generated by all fields in the analysis function are ignored. |
Dashboard | Linkage Jump Filter Component | Normal circumstance: Filtering effects of fields are ignored. Exceptional circumstance: If Linkage/Jump, Drop-Down Tree, List Tree, Tree Label, or Composite Filter is performed, it cannot be judged which specific field the operation is performed on. Therefore, CLEAN(Field) that specifies only one field cannot take effect in this case. | The filtering effects generated by all fields in the analysis function are ignored. |
Calculation Logic in DEF
1. Relationship between CLEAN and the filter conditions of inner-level DEF and same-level DEF
CLEAN does not remove the filter conditions of the inner DEF and same-level DEF because the filtering in DEF only affects the current DEF calculation.
Level | Calculation Field | Formula |
1 | A | DEF(SUM(),[],Sales Volume>100) |
2 | B | DEF(SUM(A),[],[CLEAN(Sales Volume)]) |
3 | C | DEF(SUM(B),[],[Sales Volume<300]) |
In this case, CLEAN does not affect the filtering of the sales volume in levels 1 and 3.
2. CLEAN parallel to the same-level filtering
CLEAN is parallel to the same-level filtering. It clears the upper-level filtering while allows the same-level filtering to proceed as usual.
For example, in DEF(SUM(),[],[CLEAN(Date),Date="2022"]), the result filter is bound to Date for filtering, and at this time, Date="2022" is used in DEF for calculation.
Case Application
Example
You can download the sample data: Product Sales.xlsx.
1. Upload the data and analyze the sales of the products. Drag the field Order Amount into Indicator, set Summary Mode to Maximum, and rename the field Maximum Sales Amount per Order.
2. Analyze the sales data of the orders in July only. Drag the field Order Date into Result Filter, click the icon next to the field, and select Year-Month from the drop-down list. Click the icon again, select Filter from the drop-down list, click Add Condition, and select Order Date, Fixed Value, and 2022-07 in sequence.
Add a calculation indicator named Maximum Sales Amount per Order in History to add a column to display the data of the maximum sales amount per order in history for comparison with the data in July.
Scenario | Formula | Description |
You want the calculated result not to be affected by order date filtering. | CLEAN(Order Date) | The calculated result will not be affected by the filtering performed on the value of the field Order Date through the component filter and dashboard filter component. |
You want to calculate and obtain the maximum value of the product sales amount per order in history. | DEF_ADD(MAX_AGG(Order Amount}),[],[CLEAN(Order Date)]) | Dimension: Product Indicator: Order Amount Condition: ignoring the filter conditions of the field Order Date |
The following figure shows the component effect.
The following figure shows the dashboard effect.
More Examples
Formula | Result | Remark |
DEF(SUM_AGG(Indicator),[Province,City],[CLEAN_WIDGET(City)]) | The filtering effects generated by the field City are ignored during the DEF calculation. | The filtering effects generated by the field City are ignored in Result Filter/Linkage/Drill/Jump/Filter Component. |
DEF_ADD(SUM_AGG(Indicator),[Dimension 1,Dimension 2,...],[CLEAN("ALL")]) | All filtering effects are ignored during the DEF_ADD calculation. | All the filtering effects generated by fields are ignored in Result Filter/Linkage/Drill/Jump/Filter Component. |
