Is there a term for generating time-line based data from individual points? Also how would I do this?

Question

Is there a term that describes what I'm trying to do below? Also, how would you do this using something like JMP or Excel? (or do I need to code this in something like perl?)

Given this sort of data:

ID| opened     | closed     | quantity
--------------------------------------
1 | 2010-01-01 | 2010-01-03 | 1
2 | 2010-01-02 |            | 2
3 | 2010-01-02 | 2010-10-05 | 3

I'd like to get this data and then graph with x being a time line and Y being total quantity open:

on date    | total quantity open
---------------------------------
2010-01-01 | 1
2010-01-02 | 6
2010-01-03 | 5
2010-01-04 | 5
2010-01-05 | 2 

Answer 1

Use SUMIF() to compute the total open to date and the total closed to date. The difference at any time is the total currently open.

Let's suppose the data you show are in the range A1:D4 in Excel. Reserve four columns for your output: the two shown plus two for intermediate calculations. Let's suppose they are columns E:H. The formulas are:

Column E has the dates in ascending order exactly as shown in your output. Column F is computed by propagating this formula from F2 down as far as needed:

=SUMIF(B$2:B$4,"<=" & $E2,$D$2:$D$4)

(Extend the row index "4" as far down as needed to cover your data.)

Column G is computed by propagating the formula from F2 over to G2 and then down. For example, the formula in G3 will be

=SUMIF(C$2:C$4,"<=" & $E3,$D$2:$D$4)

Column H is the difference of columns G and F: it contains the results you need.

My spreadsheet looks like this:

ID  Opened  Closed  Quantity    Date    Open    Closed  Net
1   1/1/2010    1/3/2010    1   1/1/2010    1   0   1
2   1/2/2010                2   1/2/2010    6   0   6
3   1/2/2010    1/5/2010    3   1/3/2010    6   1   5
                                1/4/2010    6   1   5
                                1/5/2010    6   4   2

The translation to R is straightforward for those who prefer that environment.

Answer 2

One simple algorithm which could be implemented easily would be:

Step 1: Add variables to your data frame; one for each time you want to graph. In pseudo code

if time_i >= opened AND time_i < closed then quantity else 0

Step 2: sum the rows for each generated time variable to get the quantity at each time

Step 3: It would then be straight forward to graph the resulting data.

All these steps would be straightforward in Excel, R, or any number of other programs.