Create MTD data out of YTD values

KNIME Analytics Platform
#1

Hello Knime Community,

I’m currenty trying to create a workflow to display our reporting figures. The data input table consists of the YTD Values of different KPIs for the different Business Units and Regions. Each KPI is in a different column as well as the indicator for the Business Unit and for the region are in separate columns. Now I would also need the values on a MTD level to be created. I therefore need a loop which looks for the value of the previous month for the same Business Unit and Region and substracts this from this month. This needs to be done for every month and every KPI.

Does anyone have an idea of a smart way to do so?

Thanks a lot in advance!

#2

Hi there @Steffi01,

can share example of your input file and expected output? Dummy data is fine as well. It would help as solution for these kind of tasks highly depend on data format :wink:

Br,
Ivan

2 Likes
#3

Hello Ivan, thanks for your reply!
I extracted an example of the desired Layout. The Input looks the same but only with YTD information.

Thanks for your support!

Reporting-Knime.xlsx (19.4 KB)

#5

Hi @Steffi01,

Just so i understand you correctly.
In your example e.g. for germany
For Feb you would calc:
Gross Profit
231815-364675
Profit
(-78791)-(-84555)

and this done for all your KPIs?

#6

Hello,

no that’s not correct. The Gross profit for Februar for Germany and BU 06-01 would be calculated by substracting the YTD value of November (282388) by the YTD value of October (191842). This has to be done for every KPI but also take into account the respective Business Unit and the respective Region.
Hope this helps with the understanding… thanks!

#7

Hi Steffi01,

maybe the following workflow will help you with your problem.
The Lag columns node is used to get the value from the previous months:


GroupLoop is used to only calculate the lag on your defined Business Unit/Region

*Sort Node is used to ensure that the rows are in correct order for the lag node

Upper part you will have to manually configure for every KPI you want to transform
Lower part will loop through all KPIs selected (but is overly complicated - thought it would be easier)

grafik
grafik1324×874 60.3 KB

KNIME_project8.knwf (59.1 KB)

*also I think your example switch months and BU…so for clarity I switch it to the cortrect columns :slight_smile:
Reporting-Knime.xlsx (5.5 KB)

Output looks something like this:

grafik
grafik1012×215 11 KB

3 Likes
#8

*also noticed that you neither liked or marked as solved the answers to your previous two topics
I think you owe at least one of both to:

for their responses in your previous topics :stuck_out_tongue:

1 Like
#9

Thanks for your help! I had to adapt a few things but the idea was great and worked! :slight_smile:

2 Likes
#10

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.