Dear KNIME community,
I been struggling to find out a way how to add conditions if in the cumulations
as this table below that i have the workflow to run the cumulation by sub category → column “Sum(Qty)”
But I need the condition to verify if the result of the cumul >0, then will be as 0 in the next level cumulation → as column “Condition Cumul Qty”
Thank you for your guidance, appreciated it!
KR,
Chrystal
Condition ■■■.knwf (14.4 KB)
Here is the workflow, and the “Condition Cumul Qty” is the column that expect to have, thanks a lot!
Hi @Chrystallu , welcome to the KNIME community. I’ll take a look at this, as I’m sure others will. In the meantime, may I suggest you use a different abbreviation for cumulative in your post (e.g. “cumul” instead of just the first three letters) to prevent it being redacted by the forum software’s profanity filter 
I’ll see if I can modify your posts accordingly to make them readable 
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:
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!
An alternative approach uses a component that I called the Cumulative Framework, but you don’t actually use it quite like a regular component, as you unlink (disconnect) it and then modify a Rule Engine and Math Formula contained inside it. It is built using 3 other components that work with a recursive loop. Those workings are not something you need to know about though.
This workflow contains a demo of the cumulative framework providing the same solution as the Column Expressions example.
Conditional Cumulative Demo with Cumulative Framework.knwf (134.9 KB)
The method for using the Cumulative Framework component is best demonstrated with a demo video (it contains no audio). Apologies that the video quality is not great because it had to stay under the forum’s 4MB limit.
If you are interested, the “cumulative framework” is described further in my forum post from December 2023…
Hi @takbb , thank you for you adjustments
Hi @takbb , thanks for the detail replied, and I also the “Cumul Framework” node also amazing!!
The only thing is that the prev row cumul just as a condition 0 in the next running, not directly equal to 0 (this logical may visible in the excel table)
Also this cumul sum need to sub by category and color this two column
Is this logical could be achievable in the Cumul Framwork node? Thank you!
BR,
Chrystal
Hi @Chrystallu , if I understand correctly, you want the following alterations:
The cumulative calculation to reset if the Product and/or Colour changes.
The calculation of the current cumulative quantity also differs from my previous understanding in that we should only add the previous cumulative quantity to the current quantity if it is not greater than zero.
Let’s look at (1) first…
With the cumulative framework, that is simply a case of adding an extra rule so that it resets the cumulative value to 0 for either of those values changing, as the framework itself automatically provides the previous value for each column via the additional PREV#xxxxx column references without you having to do anything other than execute the component so that it can create them. Add the additional line to handle colour change below the Category change rule:
NOT $PREV#Colour$ = $Colour$ => 0
i.e.
For (2), to change the calculation for cumulative quantity, this is a simple change to the Math Formula to this:
if($PREV#Condition ■■■ Qty$ > 0, $Qty$, $PREV#Condition ■■■ Qty$ +$Qty$)
redacted text from forum filter reads:
For the Column Expressions example, the changes are still relatively simple for (1) though you have to do include additional lines of code to keep track of the colour too, so there is a little more editing to do, and the “sum” calculation can be adjusted in line with the change for (2)
/* 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 lastColour
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=""
lastColour=""
cumulQty = 0
}
/* --- end of initialisation section---*/
if (column("Category")!= lastCategory || column("Colour")!= lastColour )
{
// reset cumulsum on change of category
cumulQty = 0
}
/* remember values for use with the next row: */
lastCategory = column("Category")
lastColour = column("Colour")
// reset cumul sum if it exceeds 0
if (cumulQty > 0) {
cumulQty = column("Qty")
}
else{
cumulQty+=column("Qty")
}
//---------------------------------------------
/* the value to go in ConditionCumulQty */
cumulQty
//---------------------------------------------
So you can see there are extra lines to:
Let me know how you get on with the above adjustments. I haven’t re-uploaded the workflows as making the changes should be straightforward and will assist with understanding, but I’ll do so if you run into difficulties.
Hi @takbb , almost there, the “Condition Cumul Qty” column is the column that I want to add new by using those conditions, seems this column cannot be use as references in the calculation, correct?
this logical is that
1, I wanna to add a cumul column by column D (Qty) to have an expect new column E (Condition Cumul Qty)
2, in order to have column E to add condition during the cumul process if the result is positive, then this positive number will be as 0 in the next row running (Like row 10 to 11, if the ■■■ result in row 10 is 250 and this is >0, then row 10 for the cumul result still is 250, but in the next row 11 cumul result will be -500+0=-500 )
Sorry to make it not so clear at the beginning, thank you for your explanations!
BR,
Chrystal
@takbb sorry forget about this (i cannot delete it at the moment), the column expression node could solve this
Thanks a lot for your detail explanations, it helps a lot!
BR,
Chrystal
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.