My purpose is that, each of username will have unique amount per month. And it need to be returned based on the Total from the latest date to the back.
If this month is May, then the first date to check is April, then March → Feb → Jan …
If that month (latest month) can cover the amount (Total >= amount), then the expected result is equal amount.
If it can not cover, then write the amount that month can partly cover. For example, case America, if that month have 200 (Total), then we write 200 in the expected result and then check the rest amount (1,500,000-200,000=1,300,000) in the rest months.
We will stop checking if Sum of Expected result equal unique amount of that username, the rest month after stop will return value “0” or blank.
Assume we will have 10 months or 100 months (many years need to be checked).
I prefer using looping or coding is faster and shorter.
Date format: YYYY-MM-DD
I hope you guys can support me on that.
Once again, I appreciate every contributions of you guys.
I attempted to solve this use case by employing two loops. The first loop iterates through each user_id, while the second performs the calculations using a recursive loop. Throughout the process, I updated the amount column. If you prefer not to modify the existing column, you can create a new temporary column to store the values. Here is the workflow:
I’ll try to explain this process as clearly as possible. Here’s how the calculation is done:
Within the loop, the data is sorted by date in descending order. The top row is used for calculating the result, and the remaining rows are sent back to the next iteration of the loop, with the amount column updated with the rest value (calculated in the steps below).
A Math Formula node calculates the rest value, which is the difference between the amount and the total.
A Rule Engine node creates a case switch variable (named IF) to direct the flow based on whether the rest value is more than zero. If the rest value is greater than zero, the upper part of the switch is activated; otherwise, the lower part is activated. The lower part sets the result to 0 using the Rule Engine node, while the upper part checks if the rest value is still greater than 0. If it is, the total is used as the result; otherwise, the rest value is used as the result.
Finally, the rest value is passed back as the updated amount column.
. I’ve never used the loop before.