Running total with a maxium

Question

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,

test.yxmd

LordNeilLord · Accepted Answer

Hey @Yury_Brunt

Mutlirow Formula is your friend here:

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome

MarqueeCrew · Accepted Answer

@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