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

#beginners #tutorial #programming #productivity

Problem description & analysis:

Here below is an annual and quarterly sales data 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)

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: