# Looping calculation

Hi everyone, Im new to KNIME. Im working with this kind of data and my purpose is that I want to find month (or months) needed to cover the ‘amount’.

I want to check if the value of months can cover the value of ‘amount’ column or not.

The flow is, I have to check the value of the latest month first, if it larger than the ‘amount’, return the name of that month (if it can be formatted to Date is perfect)

If the latest month can cover a part of ‘amount’, then check the previous month, then return the name of all the months that need to cover the ‘amount’

Edit: Data in Apr, Mar, Feb is Number (Double). I forgot to change it.

Welcome to KNIME Forum. This was not easy (at least for me ) . I hope it covers all your possible combinations, (I added some extra cases). So check the results with your real world data. See this wf looping_calculations.knwf (82.8 KB)

gr. Hans

4 Likes

If you want to go the coding route, you can do this with one Column Expression node.

``````var cumulativeSum = 0
var colNames = []
var columnList = arrayRemove(columnNames(), 0, 1)

for (var i = 0; i < columnList.length; i++) {
cumulativeSum += column(columnList[i])
colNames.push(columnList[i])
if (cumulativeSum >= column("Amount")) {
break
}
}

result = colNames.join(", ")
``````

This checks when the cumulative sum is higher than the amount and subsequently concatenates all the months it had to go through in order to satisfy the threshold.

5 Likes

Continuing the discussion from Looping calculation:

Hi @HansS and @ArjenEX

I appreciate your solution a lot. I’m so impressed with your solution.

Your solution was very awesome, although it took me a while to understand haha. But so far it covered almost everything I want.

However, I found some minor mistake if the data unclear, the flow will be affected somehow.

I attached this excel file to broaden the situation that can be covered.

The thing is, I want to check to latest date first, then others will go after.

1. Take the Sum of latest date of that username. If it = 0, take the date after the latest date to test.
2. Compare the amount vs Sum. If amount <= Sum, write amount in the new column, else Sum (x)
3. If (x) exist, take amount - Sum of the date in (2.) and compare it with the Sum of the date after (2.). If [amount - Sum of the date in (2.)] <= [Sum of the date after (2.)] then write amount - Sum of the date in (2.), otherwise, write [Sum of the date after (2.)]. (y)

if claude else (y) of case (3.) exist, take amount - Sum of all previous date of that username and compare it with Sum the third date. if it <= Sum the third date, write [amount - Sum of all previous date of that username], write Sum of the third date.

Keep doing that until the sum of the amount in new column = the unique amount of each username

Date format: YYYY-MM-DD

<>

I hope you guys can support me on that.

Once again, I appreciate every contributions of you guys.

data.xlsx (10.1 KB)

Hi @huynhduc , I’ve uploaded a workflow to the hub which is an alternative solution for the problem you first posed. I made use of the sample data set provided by @HansS in his uploaded workflow.