7.11 Calculate using adjacent row/interval when data of the same group is discontinuous (LRR/YOY in the case of missing data)
Here below is an annual and quarterly sales data table:
In this table, the data of the first quarter in 2020 is missing. When the data of this quarter is used to calculate LRR, skip this quarter directly, and use the data of the fourth quarter in 2019; when the data of this quarter is used to calculate YOY, regard it as zero (calculate in the cell D1):
=spl("=E(?1).new(Sales-Sales[-1]:LinkRelative,Sales-~[:-1].select@z1(Year==get(1,Year)-1 && Quarter==get(1,Quarter)).Sales:YOY)",A1:C20)
Where, get(1,Year) means taking the value in the column Year of current member of previous-layer function.
~[:-1] represents the set from the first member to the previous member.