8. Lag Column Node

<p><strong>This workflow and its description are based on the example linked below.</strong><br><br>In this example, we perform a Time-over-Time analysis using a handful of KNIME nodes.<br><br>Taking as input a spreadsheet of profits over time, we are filtering by country and calculating the percentage difference in profit between each year and the two years prior to it.<br></p><p><br>Below are more details regarding the top nodes involved:<br><br>To view the configuration for each node, either Double-click or right-click &gt; "Configuration" to open the configuration dialogue.<br><br>Excel Reader node:<br><br>1 - In the "File and Sheet" tab, the file name and the sheet to read from are already set up (the folder with the data is already included when you download the workflow).<br><br>2 - Within the "Data Area" tab, we have selected read the "whole sheet" since the spreadsheet already has a standard format (non-empty rows or columns, etc.)<br><br>Reference Row filter node:<br><br>1 - We take the column "country" both as a Data column and a Reference column. <br><br>2 - We include only rows from the reference table in the output. Consequently, we will have an output table with data of only one country,<br>"Italy". You can change this value directly in the Table Creator node.<br><br>Lag Column node: <br><br>This node is the most critical in the workflow. Since we need to calculate the percentage differences for each year from the previous two, we are creating two helper columns that enable us to accomplish this task.<br><br>1 - The column to lag or shift is "profit".<br><br>2 - Since we need to generate two columns to perform our calculations, we set up two as a Lag value.<br><br>3 - The Lag interval will be 1. This way, we will have the profit for the current year, the previous year, and two years ago all in the same row.<br><br><br>Math Formula (Multi Column) node:<br><br>1 - Include in the calculation only the two shifted columns previously generated.<br><br>2 - In the expression, we will use the placeholder $Current Column$ to calculate the percentage difference for both columns. <br><br>3 - We flag the option "Replace Selected Columns".<br><br><br>Excel Writer: <br><br>In the workflow's lowest branch, we generated a sheet name using the String Manipulation node and passed the value as a flow variable to the Excel Writer node. We will append a new sheet to the original file using the flow variable as the sheet name.</p>