EARLIER: gets value of the current row. It can only be used in DEF functions, and is generally used for inter-row filtering.
Field column
Parameters do not support constants, and you can use reference functions.
Problem description: When the data is updated, the system will prompt update failure. The error message is as follows: During the update, it was detected that the inequality of the earlier function was used, resulting in severe expansion of the calculation data, and the operation was aborted. Please check the earlier function in the formula, remove the earlier inequality or control the size of the data.
Cause analysis: The earlier function has data restrictions on the self-service dataset. The preceding error message will be displayed if the data volume of the self-service dataset exceeds 1,000,000.
Solution: Use other functions instead, or limit the data volume of the underlying database table to 1,000,000.
In many cases, you need to calculate the offset value, such as the difference between the previous date and the next date or the conversion rate between the data in the previous step and that in the next step.
Taking the calculation of the conversion rate as an example. You need to obtain the value in the previous row to facilitate the division of the values in the previous row and the current row (displayed in two columns).
1. Upload sample data: Auto Sales Funnel.xlsx.
Create a subject and upload the sample data, as shown in the following figure.
2. Calculate the number of customers of each step and add the calculation field, as shown in the following figure.
3. First rank these steps according to the number of customers. Since these steps have a sequence, the larger the number of customers, the higher these steps ranks in the sequence.
DEF (COUNTD _ AGG (Key Step), [Key Step], [Number of Custom of Each Step > EARLIER (Number of Custom of Each Step)) +1
Specified dimension: Key Step
Aggregated indicator calculation: COUNTD_AGG(Key Step)
Filtering conditions: Number of Custom of Each Step > EARLIER (Number of Customers of Each Step), which means filtering all steps whose number of customers is greater than number of customers of the current step from the data.
Calculate the number of steps with a number of customers greater than number of customers of the current step, and add 1 to obtain the step ranking.
After dragging the Ranking into the analysis area, you can check whether the Ranking calculation result is correct.
4. Calculate the number of customers of the previous step relative to the current step, as shown in the following figure.
SUM_AGG(Number of Customer)
Calculates the total number of customers.
DEF (SUM _ AGG (Number of Customer), [Key Step], Ranking + 1 = EARLIER (Ranking))
Aggregate indicator calculation: SUM_AGG(Number of Customers)
Filtering condition: Ranking + 1 = EARLIER(Ranking)
The filtering condition is Ranking = EARLIER (Ranking) - 1, filter to the step with a ranking one place ahead of the current row ranking, and then obtains the sum of the number of customers of that step.
Drag the Number of Custom of Last Step into the analysis area to see if the calculation results are correct, as shown in the following figure.
5. Calculate the conversion rate using the formula: Number of Customer/Number of Custom of Last Step, which can be calculated, as shown in the following figure.
6. Drag the Conversion Rate into the analysis area and set Numeric Format to Percentage, as shown in the following figure.
The conversion rate analysis is completed.
滑鼠選中內容，快速回饋問題
滑鼠選中存在疑惑的內容，即可快速回饋問題，我們將會跟進處理。
不再提示
10s後關閉
Submitted successfully
Network busy