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.
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)])
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.
Field Filtering
CLEAN("ALL")
Result Filter
Drilling
Field filtering effects are ignored.
The filtering effects
generated by all fields in
the analysis function are
ignored.
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.
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.
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
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.
Remark
DEF(SUM_AGG(Indicator),[Province,City],[CLEAN_WIDGET(City)])
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
滑鼠選中內容，快速回饋問題
滑鼠選中存在疑惑的內容，即可快速回饋問題，我們將會跟進處理。
不再提示
10s後關閉
Submitted successfully
Network busy