Rolling accumulation

Hi Dicken 

Give this a go

let
    alist = {1,2,3,1,2,3,2,2,2,2,3,3,2,2,2,2,2,3,3,2,2,1},
    rolling =
        List.Accumulate(
            alist,
            [a={}, b={}],
            (s, v) => [a=List.LastN(s[a] & {v}, 4), b=s[b] & {List.Sum(a)}]
        )[b],
    result = Table.FromColumns({alist, rolling})
in
    result

Hi Dicken 

 

Beside the amazing function using List.Accumulate, presented by m_dekorte , this is the List.Generate version you can use.

 

let
    Query1 = let
  alist   = {1, 2, 3, 1, 2, 3, 2, 2, 2, 2, 3, 3, 2, 2, 2, 2, 2, 3, 3, 2, 2, 1},
  rolling = List.Generate(()=> 1, each _<=List.Count(alist), each _+1,each List.Sum(List.LastN(List.FirstN(alist,_),4)))
in
  Table.FromColumns({alist, rolling})
in
    Query1

Hi Dicken,

 

Once you know this trick it is easier to understand 😉 let me try to explain the inner workings.

 

The seed s is a record containing two fields, a and b, both assigned an empty list.

At each iteration:

• Think of the field a as a rolling window that holds up to the last four values that were seen. It’s updated by appending the current value {v} from alist to the previous list state s[a], then trimmed to keep only the most recent four items List.LastN(..., 4)

• Once a is updated, we calculate its total using {List.Sum(a)}. That number gets added (apended) to the previous list state s[b], which is a growing list of those running totals.

After the accumulation completes, the field [b] contains the full sequence. A list showing what the sum of the last four values was after every step.

 

 

I hope this helps to visualize the process, Richard.