Time-over-Time Analysis

In this example, we perform a Time-over-Time analysis using a handful of KNIME nodes.

Taking as input the Olympics Games Medals by country spreadsheet, we are filtering by country and calculating for each edition the percentage difference versus the last two ones in terms of total medals won.

Below are more details regarding the top nodes involved:

To view the configuration for each node, either Double-click or right-click > "Configuration" to open the configuration dialogue.

Excel Reader node:

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).

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.)

Reference Row filter node:

1 - We take the column "country" both as a Data column and a Reference column.

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,
"Italy". You can change this value directly in the Table Creator node, checking other countries' total medals.

Lag Column node:

This node is the most critical in the workflow. Since we need to calculate the percentage differences for each Olympic Games edition from the previous two, we are creating two helper columns that enable us to accomplish this task.

1 - The column to lag or shift is "total". It is the total of medals by edition.

2 - Since we need to generate two columns to perform our calculations, we set up two as a Lag value.

3 - The Lag interval will be 1. This way, we will have the total medals by edition, the total medals from the last edition, and the total from the previous one in the same row.

Math Formula (Multi Column) node:

1 - Include in the calculation only the two shifted columns previously generated.

2 - In the expression, we will use the placeholder $Current Column$ to calculate the percentage difference for both columns.

3 - We flag the option "Replace Selected Columns". The final output will be the total medals and difference percentage from the last and previous one for each row (each row is an Olympic Games Edition).

Excel Writer:

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 to allow us to create a custom sheet ( medals trend).

We will append a new sheet to the original file using the flow variable as the sheet name, in the example "Italy medals trend".