Sorting According to Specified Rules in Data Editing

Problem

Analysis functions (for example, the WINDOW function) can only be output and used directly in the components.

For example, you can use the RANK_AGG function to sort the data according to specified rules. After the data is grouped by Contract Payment Type, you can obtain the descending rank of amount for different contract types within each group, as shown in the following figure.

If the function is directly output on the data editing page, a prompt "The aggregation functions cannot be output directly." is displayed, as shown in the following figure.

How to sort the data according to specified rules in data editing?

Cause

Both the RANK_AGG and WINDOW functions are aggregation functions that can be used to perform calculations directly based on the analysis view. However, data editing is based on detail calculations, which cannot provide a valid view for the RANK_AGG and WINDOW functions.

For example, the rank in section "Problem" is obtained by RANK_AGG([Contract Payment Type],[SUM_AGG(Contract Amount),"desc"],"UNIQUE"). After you group the data by Contract Payment Type, the data in the analysis view will be sorted in the descending order based on whichever field is dragged into the Dimension bar.

If the Contract Type field is dragged into the Dimension bar, the contract amount will be grouped and aggregated by Contract Payment type and Contract Type in sequence, and a descending rank will be obtained based on the contract amount.

If the Customer ID field is dragged into the Dimension bar, the contract amount will be grouped and aggregated by Contract Payment Type and Customer ID in sequence, and a descending rank will be obtained based on the contract amount.

You need fixed grouping fields in data editing to obtain a fixed rank. Therefore, you cannot directly use the RANK_AGG ranking formula added by component calculations in data editing.

Solution

You can use the DEF function to set up an independent view. If nesting the WINDOW function inside the DEF function, you can provide the WINDOW function with a specific view for calculation.

You can select Formula Column on the data editing page.

The formula obtained by nesting the WINDOW function inside the DEF function is DEF (Aggregation indicator/Calculation result of the WINDOW function,[View dimension 1 that the WINDOW function depends on,View dimension 2 that the WINDOW function depends on]

Contract Amount Ranking Within the Group is obtained by DEF(RANK_AGG([Contract Payment Type],[SUM_AGG(Contract Amount),"desc"],"UNIQUE"),[Contract Payment Type,Contract Type])

The following example introduces the specific procedures.

Download the sample data Contract Fact Table.xlsx.

Data Processing

1. Create an analysis subject and upload the sample data. For details, see Getting Started with FineBI.

2. Select Group Summary and drag the corresponding fields into the Group and Summary bars for sorting, as shown in the following figure.

3. Sort the data by Contract Payment Type, and then sort the data in the descending order by Contract Amount. In this case, you can easily verify the calculation result of the formula later, as shown in the following figure.

Formula Input

Enter the calculation formula DEF(RANK_AGG([Contract Payment Type],[SUM_AGG(Contract Amount),"desc"],"UNIQUE"),[Contract Payment Type,Contract Type]), as shown in the following figure.

Effect Display

The following figure shows the effect.