Time-over-Time Analysis
In this example, we perform a Time-over-Time analysis in KNIME Analytics Platform.
We observe the Olympics Games Medals spreadsheet, which contains the number of medals won (bronze, silver, gold, total) by country and Olympics Games edition. We then filter the data to one country (here: āItalyā) and calculate the percentage change regarding the total number of medals won compared to the two previous games.
💡 To view each node's configuration, select the node and see the configuration pane on the right side of the workflow editor.
Let's walk through the different nodes involved in this operation:
Excel Reader node:
Since the folder with the data is already included when you download the workflow, in the "File and Sheet" tab, we choose to "Read from" the "Current workflow data area" and select the dataset.
In the "Data Area" tab, we select to read the "Whole sheet". This configuration allows us to have the sheet as it is. The intent is to respect its original structure.
Table Creator node:
We use the Table Creator node to manually create a country name value which we can use to filter the input data.
We create table with two columns: "country" with the value "Italy"; and "sheet name" with the value "medals trend".
Reference Row Filter node:
We filter the input data using the manually created table as reference. The table to be filtered goes into the top input port, the reference table into the bottom input port: Data column: country; Reference column: country
We choose to "Include" matching rows from the reference table. 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 to see results of other countries.
Column Filter node:
We continue by only keeping the year and the total medal count. We filter out all other columns.
Lag Column node:
This node is the most critical in the workflow. We use it to create two shifted columns which we will use to calculate the Time-over-Time comparison.
Column to lag: total (the total number of medals won - our value of interest)
Number of copies: 2 (we need two helper columns because we want to calculate the difference to the last two previous games)
Lag per copy (= lag interval): 1 (specifies, how many cells to shift the value
After executing this node, we now have for each Games edition the total medals ("total") as well as the total medals of the two previous games ("total(-1)" and "total(-2)") in one row.
Math Formula (Multi Column) node:
We are calculating the percentage change of the two shifted columns to the "total" column.
Include only the two shifted columns in the Include panel.
In the expression, we use the placeholder $Current Column$ to calculate the percentage difference for both columns.
We flag the option "Replace Selected Columns".
The final output will be the total medals column and the percentage change in medals compared to the previous two games. Note. You can also use an Expression node instead and create one expression for each calculation.
Column Renamer node:
We rename the columns for better understanding.
Expression node:
We create custom sheet names by combining the value of the "country" column with the value of the "sheet name" column (from the manually created Table).
We choose to overwrite the value in the "sheet name" column with the new value.
Table Row to Variable node:
We convert the "sheet name" column to a flow variable. We will use this flow variable in the Excel Reader node to overwrite the setting.
Excel Writer node:
We append the resulting dataset to a new sheet in the existing dataset. We select to "append" the existing file and use the flow variable "sheet name" to control the name of the sheet. The sheet name changes whenever we change the values in the Table Creator node.
To set the sheet name to the flow variable, we go to the "Flow Variables" tab in the configuration window and select the variable "sheet name" in the "sheet_names" setting.
After executing the node, the file will open automatically.