How can I prevent a running backlog total beastmode from hitting zero?

TylerMarshall

I'm working on building a labor model for my department to track where our projected backlog will be over the next several weeks. One problem I'm running into is when the incoming backlog is less that what we're projected to process, the backlog goes into the negative instead of hitting zero which then throws off all subsequent projections. Any idea on how to rewrite this so that the total will never drop beyond 0?

Below is a simplified version of the beastmode I currently have written

/*current total backlog*/

SUM(SUM(`backlog per unit`)) OVER()

 +

 /*Incoming/Projected backlog Beastmode*/

SUM(SUM(`incoming backlog`)) OVER (ORDER BY `week starting`)

-

SUM(SUM(`units processed`)) OVER (ORDER BY `week starting`)

    

MichelleH

@TylerMarshall How is your data grouped in the card? You many need to add a PARTITION BY and/or ROWS clause in your window functions