Substract actual Value from previous Value under certain conditions

[ARock1980]

Hi Community,

i have following problem:

Every month i get a table with new Data and i calculate the difference to the previous moth for a list of KPIS.

Is it possible to create a Workflow which says:

1. In Periode 3 (P03) look for “BU”, “Reporting Unit Name”, “Commodity” and “Consolidation Step”
2. Search this line in Period 2 (P02)
3. Calculate the difference for every KPI in an seperate Column.
4. Do the same with the Data when Periode 4/5/6… is available

Difference Period.xlsx (17.8 KB)

[kabrah]

Hi @ARock1980,

It is definitely possible to create such a workflow, the interesting part will be how you choose to build your solution

The simplest way to solve your problem would be to use a group loop and iterate through the condition columns, then use the lag column node to add the previous KPI value to the row and subtract the two values to get the difference. You can do this lagging and subtracting for each KPI column individually or loop through the KPI columns as well, so that you don’t repeat the steps (although the nested loop might be a bit of overkill).

I’ve added a workflow to show how one could solve this problem with a nested loop. Hope this helps, feel free to reach out if something is unclear!
forum_9Jan23.knar (54.1 KB)

Best,
Karen

[mwiegand]

Hi @ARock1980,

I love the principle of “divide and conquer”. Here is a possible approach which:

1. Creates a Classification to filter based on your desired columns
2. Splits rows apart which have no preceding period
3. Loops per Period combined with the Classification
4. Splits regular from “calculation” column and loops over each
5. Creates a rule and filters based on the current Classification and Period (-1)
6. Crunches numbers
7. Reconstructs the table

Conditionally substract current from previous value – mwiegand

Let me know if that works as desired?

Best
Mike

[ARock1980]

@mwiegand im sorry, but i cant put in the workflow via dragg and drop from the mentioned site. can you share it here pls too or do you know why it doesn’t work?

[mwiegand]

I have no clue, maybe ad block? You could however download it the “usual” way

[ARock1980]

hi @mwiegand no it works

I recently added your WF to my data and it looks fine

Thanks alot for your help!

[ARock1980]

Hi I checked the WF mit my real data and it works. If I have further questions i’ll ask you. Thanks alot