Duration based aggregation

Hello everyone,

in my data I have the following simplified table structure.

startdate enddate sum
2012-11-26 2012-11-28 5
2012-11-27 2012-11-30 2
2012-11-29 2012-12-01 3
... ... ...

Now what I want to get is the value from [sum] calculated for each single day. E.g. for 2012-11-27 it would be 7 (5 + 2). Is there a straight forward solution to do that in KNIME that I am just missing?

Thanks in advance for helping. 



go with the "Moving Aggregation" node and check the "cumulative computation" option. Then create a manual aggregation taking the sum of the column "sum" and you should have your desired results.

Best wishes, Johannes


first of all thank you for your fast reply. 
Unfortunately your hint won´t solve my problem.

Maybe my example was to generic so let me explain it more precisely. 
The value in the column "sum" is the occupancy somewhere. Between 2012-11-26 and  2012-11-28 5 people booked something. 

2 people booked something between 2012-11-27 until 2012-11-30. 

And now I want the final occupancy for the 2012-11-27. 

How I get it? Of course the 2 people from above and also I have to add the 5 people who booked between 26th and 28th, because they include the 27th. Final occupancy is 7.


Finally I need this table structure:


date occupancy
2011-11-26 5
2011-11-27 2
2011-11-28 2
2011-11-29 5

Any possible solutions how to solve this kind of problem?

Thank you very much.




ok... yeah that is somewhat more complex. Sorry for misreading (the cumulative sum and the sum per day were the same value so I fell for the bait ;-) )

Anyway, I have attached a workflow that solves your problem. The key is to first create one row per day for each booking period. After that it is possible to do a group by for each individual calendar date and calculate the sum of occupants on any given day.

Hope this works for you!

BTW, I believe your example table in your most recent post is incorrect as I come up with the following based on your explanation of the problem and the example in your initial post:

date occupancy
2011-11-26 5
2011-11-27 7
2011-11-28 7
2011-11-29 5

Thank you so much for your help and for sharing your workflow. 

It works perfectly.

And yes, you are right, I made some mistakes in the example table, thanks for bringing this up.