How to add a condition during the cumulated calculation?

KNIME Analytics Platform
4

Hi @Chrystallu , I can’t think of a method at the moment for putting conditionals within the cumulative calc with Moving Aggregator, but if you are able to use the Column Expressions node, the calculation you want is achievable

Attached is a workflow which (assuming I understood correctly) does what you want:

image
image1354×565 46.6 KB

Calculate Conditional Cumulative Qty using Column Expressions.knwf (76.5 KB)

/* This initialisation section, coded in 
 *  this way is important to making 
 *  the cumulative calculation work in
 *  Column Expressions node
 *  google:
 *  KNIME Column Expresssions have long memories too @takbb  
 *  https://forum.knime.com/t/column-expressions-have-long-memories-too/76048
 */
var lastCategory
var cumulQty

if (rowIndex()==0)
{
    /* set the value of remembered variables only on the first row (rowindex=0)
       alternatively we could use a condition such as if (lastCategory==null)...
       but using rowIndex()=0 is more explicit in our intention.
    */
    
    lastCategory=""
    cumulQty = 0
}
/* --- end of initialisation section---*/


if (column("Category")!= lastCategory)
{
    // reset cumulsum on change of category
    cumulQty = 0
}

/* remember values for use with the next row: */  
lastCategory = column("Category")
cumulQty+=column("Qty")
// reset cumul sum if it exceeds 0 
if (cumulQty > 0) cumulQty = 0
//---------------------------------------------

/* the value to go in ConditionCumulQty      */
cumulQty
//---------------------------------------------

For more info and examples of how cumulative calculations can be performed with Column Expressions, see Column Expressions have Long Memories too!

4 Likes