Overview
EARLIER: Get the value of the current row. It is only applicable to DEF, DEF_ADD, and DEF_SUB functions, generally used for inter-row filtering calculations.
Syntax | EARLIER (parameter) | Get the value of the intersection of the current row and the field column of the parameter. |
Parameter | Field column | The parameter of EARLIER is a single field and does not support constants. |
Example — Getting Values of Other Rows
In many cases, you need to get a value of another row, such as calculating the difference between the last date and this date, or calculating the conversion rate from last step to this step.
Taking conversion rate as an example, we need to get the value of the last row to get the result of division.
1. Upload the example data: Auto Sales Funnel.xlsx
Upload the downloaded data to an analysis subject.
culation indicator to calculate the number of customers of each step.
3. Rank these steps according to their number of customers. Since these steps have a relationship with each other, the more customers there are, the closer these steps are to the front.
| Formula | Description |
|---|---|
DEF(COUNTD_AGG(Key Step)+1,[Key Step],[Number of Customer of Each Step > EARLIER(Number of Customer of Each Step)]) |
Calculate COUNTD_AGG (Key Step) + 1 for steps that have been filtered. |
Drag Rank into Indicators, and check whether the results of Rank are correct.
4. Calculate the number of customers of the last step.
| Formula | Description |
|---|---|
SUM_AGG(Number of Customer) | Calculate the total number of customers. |
DEF(SUM_AGG(Number of Customer),[Key Step],Rank+1=EARLIER(Rank)) |
The filter condition is Rank=EARLIER(Rank)-1, which filters to the step that is one rank lower than the current row rank, and obtains the sum of the number of customers of that step. |
Drag Number of Customer of Last Step into Indicators to check whether the results are correct.
5. Calculate the conversion rate with the formula: the number of customers of the last step divides the number of customers of every step.
6. Drag Conversion Rate into Indicators, and set Numeric Format to Percentage.
Now the conversion rate analysis has been completed.
