Cumulative sum that resets after reaching a specific value

[I_C_Data]

Hello everybody,

the title basically says it all: I am searching for a way to reset (or restart) an accumulated sum inside a straight table as soon as it reaches a specific value (e.g. 500). Right now I am accumulating a sum with the following expression:

 aggr(rangesum(above(sum(Menge1*Artikelmenge),0,rowno())),Artikelnummer,KW)

Works perfectly fine. However I am trying to modify the expression so it will reset as soon as it hits  > 500 (Accumulation may only happen in a given range : 0-500). Help search did not give any valueable info, so I hope you guys have a clue.

Thanks in advance!

[sunny_talwar]

This might be tricky, would you be able to provide some sample data to test it out?

[jensmunnichs · Accepted solution]

How about something like

<Your expression> - ( Floor(<Your expression>/500, 1) * 500 )

Examples:
For expression result = 300, Floor(300/500, 1) will be 0, so nothing will be subtracted from the result
For expression result = 550, Floor(550/500, 1) will be 1, so 500 will be subtracted, leaving you with 50

And so on.

Not sure about syntax and everything, but the logic should work?

[I_C_Data]

Thank you very much! Works like a charm - and is actually close to my specifications.