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, andcommission_%. 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...