Icon

A template pattern for Cumulative Calculations using Standard Nodes with a Recursive Loop

There has been no title set for this workflow's metadata.

This demonstration workflow provides a "template" for building a cumulative calculation using a recursive loop.

The aim here is that only the nodes marked by Orange Annotation boxes need to be changed for this to work with just about any non-basic cumulative calculation. (By non-basic cumulative calculation, I mean any cumulative calculation that requires more than the use of the "moving aggregation" node, and normally involves a multi-column mathematical calculation).

The initial Table Creator would obviously be replaced by your input data. This must contain ALL columns that will be included during processing, so this includes an initialised placeholder column for any value being calculated. e.g. If you are calculating "Cumulative Balance" in the rules/maths nodes later, you must make sure that the Cumulative Balance column already exists at the start of processing.

The Column filter is modified to specify the columns to be retained (carried forward) to the next row's processing.

The Rule Engine and Math Formula nodes represent the place where any calculations take place. Other nodes may be used here as well as, or instead of , the Rule Engine and Math Formula nodes. The PREVIOUS row's brought forward value of any column is found in the PREV#colname version of that column. All columns included in the Column Filter at (2) will have a PREV#colname column created.

(3) Define rules and calculations. Use the PREV#colname columns inany rules or calcs requiring the previous row's brought forward value (1) Data In this demonstration, there are two projects A and B and each receives income and incurs costs.The specific aim is to calculate a cumulative sum for each project so that on each row, the current output valueshould be the cumulative balance brought forward + income - costs.But the primary aim of this workflow is to do this in such a way that it is reasonably generic.i.e. By modifying just the regions annotated in Orange, the calculation would work for a different set of data,with different column names. To achieve this, the non-orange nodes provide framework support for retainingthe "PREV#colname" columns which represent the value of a column retain on the previous row.The Blue annotations now mark the points where User Input Data and Rules/Calcs should be connected (2) Choose columns to beretained for next row Connection point.Connect Input data here Connection point.Connect this to Rules/Calcs Connection point.Connect Rules/Calcs to here This is the table being calculated. It must contain a "placeholder" for any newcolumn that is to be calculated. i.e the column must be present hereiterate rowsperform calculationincludes useof previous row valuebrought forwardkeep only required carry forwardrow for next iterationTake the top rowfrom the table.Remaining rowsgo straight to next iterationAppend the previous carriedforward column/columns to the top rowtop port calculated rowmiddle port - remaining rows for next iterationlower port - carried forward cumulative amountsnew project?reset the carried fwd to zero!rename the columnsto be rememberedas PREV#colnameDecide which columnsyou wish to retainprevious informationabout. You can leave thisset to all columnsrename "retained" columnsas PREV#colnameremove all rows(blank table)initialise the carry-forwarddataThis table defines the valueof the column to be brought forward from previous rowInitially set to zero!automatically retain the required listof cumulative columnsextract list ofcolumn namesbeing carried forwardcollect list of thecolumn names being carriedforwardremove PREV# columns from collected resultNo OperationConvenient Single entry pointforconnecting input data toNo OperationConvenient Single entry pointforconnecting rules/calcs Table Creator RecursiveLoop Start Math Formula Column Filter Row Splitter Column Appender Recursive Loop End Rule Engine Column Rename(Regex) Column Filter Column Rename(Regex) Row Filter Add Empty Rows Table Rowto Variable Extract Table Spec GroupBy Column Filter Add Empty Rows Add Empty Rows (3) Define rules and calculations. Use the PREV#colname columns inany rules or calcs requiring the previous row's brought forward value (1) Data In this demonstration, there are two projects A and B and each receives income and incurs costs.The specific aim is to calculate a cumulative sum for each project so that on each row, the current output valueshould be the cumulative balance brought forward + income - costs.But the primary aim of this workflow is to do this in such a way that it is reasonably generic.i.e. By modifying just the regions annotated in Orange, the calculation would work for a different set of data,with different column names. To achieve this, the non-orange nodes provide framework support for retainingthe "PREV#colname" columns which represent the value of a column retain on the previous row.The Blue annotations now mark the points where User Input Data and Rules/Calcs should be connected (2) Choose columns to beretained for next row Connection point.Connect Input data here Connection point.Connect this to Rules/Calcs Connection point.Connect Rules/Calcs to here This is the table being calculated. It must contain a "placeholder" for any newcolumn that is to be calculated. i.e the column must be present hereiterate rowsperform calculationincludes useof previous row valuebrought forwardkeep only required carry forwardrow for next iterationTake the top rowfrom the table.Remaining rowsgo straight to next iterationAppend the previous carriedforward column/columns to the top rowtop port calculated rowmiddle port - remaining rows for next iterationlower port - carried forward cumulative amountsnew project?reset the carried fwd to zero!rename the columnsto be rememberedas PREV#colnameDecide which columnsyou wish to retainprevious informationabout. You can leave thisset to all columnsrename "retained" columnsas PREV#colnameremove all rows(blank table)initialise the carry-forwarddataThis table defines the valueof the column to be brought forward from previous rowInitially set to zero!automatically retain the required listof cumulative columnsextract list ofcolumn namesbeing carried forwardcollect list of thecolumn names being carriedforwardremove PREV# columns from collected resultNo OperationConvenient Single entry pointforconnecting input data toNo OperationConvenient Single entry pointforconnecting rules/calcsTable Creator RecursiveLoop Start Math Formula Column Filter Row Splitter Column Appender Recursive Loop End Rule Engine Column Rename(Regex) Column Filter Column Rename(Regex) Row Filter Add Empty Rows Table Rowto Variable Extract Table Spec GroupBy Column Filter Add Empty Rows Add Empty Rows

Nodes

Extensions

Links

Download

To use this workflow in KNIME, download it from the below URL and open it in KNIME:

Download Workflow
Created by: bbate
Created at: 2023-12-13
On NodePit since: 2023-12-14
Last update: 2024-05-02
Created with KNIME version: v5.2.0
Tags: Cumulative CalculationCumulative BalanceRecursive LoopPrevious RowBrought ForwardCarried Forward

Deploy, schedule, execute, and monitor your KNIME workflows locally, in the cloud or on-premises – with our brand new NodePit Runner.

Try NodePit Runner!