Fill /Interpolate missing values with logic challenge

I have a dataset looking like this

here is a file
test.xlsx (10.8 KB)

The goal is to fill the missing values according to restrictions
The period end values are at the bottom for each Project. In Total it’s the same value as the cumulative sum.
I now somehow need to interpolate the missing values in a way to ensure the end values (last line) are exactly met while at the same time ensure that the cumulative value for each day is summed up correctly across the projects.
Also the solution should not fill out e.g. all 38 P1 values directly at 06.01.2023. rather the values should gradually rise till the end!
Clear what the challenge is? If not let me know.
Just in case it matters this is just for one “Group”. Other groups are structured the same only thing is that the last period date differs sometimes but I guess Group loop does not care about that if that’s the way to go
Any help would be HIGHLY appreciated here.

This part confuses me. I understand it as the column P1 can only go from 0 to 39, P2 only from 0 to 13811 and that the sum of all P’s should be equal to the total of 24089. But if I sum all P’s then it equals 24311 instead of 24089.

I think it would be helpful if you can, by approximation or drawing, draft a little bit more of your desired output and indicate what is known and what should be generated, taking which restriction into consideration (horizontally, vertically, etc).

Perhaps it could be something like this:

Main thought is to use the mean value of each P columns and use that to increment the value up to the given total. First supplement all P columns with next value in the Missing Value, find the total numbers of rows and pass this as variable.

Then use round(($$CURRENT_COLUMN$$ / $${INumber Rows}$$) * ($$ROWINDEX$$ + 1),2) to update the P columns in regards to the total of that project in a multi column Math node.

Apologies I assume the sum was not correctly (happens when you just copy paste things)
P1 must go to 38, P2 must go to 13811
The sum for each row must match the cumulative cell (so up to 05.01.2023) it is 0 (meaning all values up to that point are 0 for all the projects
For 06.01.2023 the sum of the row (column P1 to P9) must be 987,975 and so on
The total result is then the 24088,9
There is an error in the dataset. P4 total is 1228 instead of 1450.
So it should match the sum of 24088,9
Thankd already for your feedback @ArjenEX
appreciate it!

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