Besides the common use cases explained in the previous sections, there are other use cases that work with groups of rows, looking for rows before or after the current one within each group.
Some examples of this are as follows:
- You have a dataset with monthly sales, group by product line. For each product line, you want to calculate the variation of sales from one month to the next.
- You have daily sales and want to infer the number of days without sales. (This is the gap in days between a date and the next in your dataset.)
- You have a dataset with a list of sales amounts and sales commissions. The fields in your dataset are sales_amount_from, sales_amount_to, and commission_%. You detected that there are overlaps in the data:
sales_amount_from, sales_amount_to, commission_%
0, 1000, %5
1001, 5000, %15
4500, 9999, %15
You want to automatically fix these overlaps. In this case, you want to change the second row to the following:
1001, 4499, %15
In all these examples, in order to calculate the necessary information, you need the values in rows different from the current one:
- In the first case, in order to calculate the variation in sales for each month, you need the sales value from the previous row (within the same product line)
- In the second example, to calculate the number of days without sales, you need the sales date from either the previous or next row. With that date and the current, you will be able to calculate the days in between them
- In the last example, to fix one row, you need to know the value of the next one
All these examples and many others like these can be solved using the Analytic Query step, which is explained in the following sections.