Rolling accumulation

[Dicken]

Can someone suggest a way to accumultate the i.e, last 4 values of a list so   { 1,1,1,2,2,1,1,2,2,1,1,2,1} = 1,2,3,5,6,6,6, etc. 

I have used Transform ;  

let
  alist   = {1, 2, 3, 1, 2, 3, 2, 2, 2, 2, 3, 3, 2, 2, 2, 2, 2, 3, 3, 2, 2, 1},
  pos     = {1 .. List.Count(alist)},
  rolling = List.Transform(pos, (x) => List.Sum(List.LastN(List.FirstN(alist, x), 4)))
in
  Table.FromColumns({alist, rolling})

  But is there a way to achieve the same using   Generate or Accumulate ?  

Richard. 

[m_dekorte · Accepted solution]

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

[Omid_Motamedise · Accepted solution]

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

[Dicken]

yes, I had just never considered a record with accumulate just Generate, in fact i would have wring the 
gernerate versionas that; 

= let alist = {2,1,3,2,2,3,3,3,2,2,3,3,2,2,1,1,1,2,2,2,3}
in
List.Generate( ()=> [ x = 1, y = {alist{0}}   ] , 
each [x] < List.Count( alist) , 
each [  x = [x] + 1,  y = List.LastN( List.FirstN( alist, x),4) ] , 
each [y] )

but supprisingly , I thing the scaler version given may have a perforance edge, mind you in real terms 
I don't think there is much in it. 

RD

[Dicken]

Thank you for both solutions,   I like both,  I have not used accumulate with a record as seed so that is something to play around with. 

Richard.

[Omid_Motamedise · Accepted solution]

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

[m_dekorte · Accepted solution]

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

[Dicken]

I like the gen version as i can understand it,   I get the accer,,  but m_d , you really must be good
at visualisig things as I have taken it apart and see how it works, but would never have been able to 
build it. 

[m_dekorte · Accepted solution]

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.