Running total with a maxium

[Yury_Brunt]

Hi,

I'm looking for a solution create a running total, only as soon the total is above a certain number (20 in the example below) the total should reset and restart at 0 one again.

Note: when the total of the values is above 20, the remaining number should be counted up to the "new" running total. I hope the example below helps te explain. Attached some sample data.

 

Thanks,

[LordNeilLord · Accepted solution]

Hey @Yury_Brunt

Mutlirow Formula is your friend here:

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome

[MarqueeCrew · Accepted solution]

@Yury_Brunt,

This formula is a little bit tricky.  @LordNeilLord pointed you to the right tool, but here's some logic to try:

IF [Row-1:RunTot] = 20 THEN 
     min(([Row-2:RunTot] + [Row-1:Value])+[Value]-20,20)
ELSEIF [Row-1:RunTot] + [Value] > 20 THEN 20
ELSE
     min(20,[Row-1:RunTot] +[Value])
ENDIF

When the RunTot is 20, it can either be "truly" 20 or it could be "capped" at 20.  The remainder needs to be carried forward.  So this logic first checks to see if the prior row has 20 for a RunTot.  If it does, then it looks to recalculate the total and find the remainder by subtracting 20 from it.  It then adds that to the current value and "AGAIN" caps it at 20 using a MIN() function.  

To configure this in your multi-row formula, you will need to modify the NumRows configuration from 1 row to 2 rows.

Cheers,

Mark

[Yury_Brunt]

Perfect, thank you @LordNeilLord and @MarqueeCrew.

I also figured out that Running total in combination with a formula > Mod([RunTot_Value], 20) will do the trick, only it is in integer and i'm loosing the remaining part of the last total as described by Mark.