Duration based aggregation

KNIME Analytics Platform
1

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. 

Cheers!

2

Hi,

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

3

Hi,

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.

 

Regards!

4

Hi,

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!

5

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
6

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.