DEV Community

Cover image for #85 — Calculate Using Adjacent Row/Interval When Data of The Same Group Is Continuous (Link Relative Ratio And YOY)
Judith-Data-Processing-Hacks
Judith-Data-Processing-Hacks

Posted on

2 1 1 1 1

#85 — Calculate Using Adjacent Row/Interval When Data of The Same Group Is Continuous (Link Relative Ratio And YOY)

Problem description & analysis:

Here below is an annual and quarterly sales data table:

source table
Task: Now we want to calculate LRR and YOY (to calculate in cell D1).

Solution:

Use SPL XLL and the code is as follows:

   A
1 =E(‘A1:C21’)
2 =A1.(Sales-Sales[-1])
3 =A1.group(Year)
4 =A3.(~.(Sales-A3.~[-1](#).Sales)).conj()
5 return A2.new(~:LinkRelative,A4(#):YOY)
Enter fullscreen mode Exit fullscreen mode

A2: Calculate LRR, [-1] represents the previous row.
A3: Group by Year.
A4: Calculate YOY. The symbol ~ represents the current member in the loop function, and # represents the sequence number of the current member in the loop function.
The results are shown below:

result table


Download esProc Desktop for FREE and watch your Excel skills level up!!!! 🚀🔥⬇️

✨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

Image of Timescale

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read full post →

Top comments (1)

Collapse
 
judith677 profile image
Judith-Data-Processing-Hacks

Stay tuned for more Excel Hacks!

🌼Discord
🌼Reddit

Image of Docusign

🛠️ Bring your solution into Docusign. Reach over 1.6M customers.

Docusign is now extensible. Overcome challenges with disconnected products and inaccessible data by bringing your solutions into Docusign and publishing to 1.6M customers in the App Center.

Learn more