DEV Community

Cover image for #86 — Calculate Using Adjacent Row/Interval When Data of The Same Group Is Discontinuous (LRR/YOY in The Case of Missing Data)
Judith-Excel-Sharing
Judith-Excel-Sharing

Posted on

#86 — Calculate Using Adjacent Row/Interval When Data of The Same Group Is Discontinuous (LRR/YOY in The Case of Missing Data)

Problem description & analysis:

Here below is an annual and quarterly sales data table:

source table
Task: In this table, the data for the first quarter of 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 (to calculate in the cell D1).

Solution:

Use SPL XLL and the code is as follows:

=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)
Enter fullscreen mode Exit fullscreen mode

get(1,Year) means taking the value in the column Year of the current member of the previous-layer function.

~[:-1] represents the set from the first member to the previous member.

The results are shown below:

result table


Download esProc Desktop for FREE and unlock powerful insights into your Excel datasheets!!! 🚀🔥⬇️

✨SPL download address: esProc Desktop FREE Download

✨Plugin Installation Method: SPL XLL Installation and Configuration

✨References to other rich Excel operation cases: Desktop and Excel Data Processing Cases

✨YouTube FREE courses: SPL Programming

Top comments (0)