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

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

-
What's the reason for the vote to close? How does this problem not relate to statistical analysis? –  whuber Oct 20 '10 at 15:31
@whuber, i don't understand the comment just above this one...it sounds like its in reply to something (that I don't see.) (I also couldn't figure out how to contact you directly to ask that...) –  Alex Oct 21 '10 at 14:59
Members with sufficiently high reputation have the option to "close" a question. This is typically done when a question is vague or off-topic. It takes five votes to close, but before all five votes are in they are anonymous. In this case somebody cast a vote shortly after your question appeared. It's considered polite to explain such votes and to provide constructive suggestions for making the question acceptable, but that courtesy was not offered in this case. –  whuber Oct 21 '10 at 15:04
Your question makes sense, from a statistical POV. I don't understand the vote to close, given that there are some very basic questions for which browsing online help would have suffice (and is educative)... and those questions were never voted down or voted to be closed. Anyway, that's community life. However, may I also remind you that you can also upvote the question you accepted as a the correct one. –  chl Oct 21 '10 at 20:48

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.

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.

-
Thanks @whuber! That's really helpful and I'm going to try it out. –  Alex Oct 21 '10 at 14:54

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.

-
So, in excel, if I wanted to look at a time frame of 365 days...then I'd add 365 columns along side the ID table? –  Alex Oct 20 '10 at 11:35
@Alex Yes. I suppose you'd need one of the more recent versions of Excel that overcomes the old column limit. And the algorithm would be more elegant in something like R. –  Jeromy Anglim Oct 20 '10 at 12:29
@Jeromy Your idea is a good one but the implementation can be greatly improved :-). I posted an explanation. –  whuber Oct 20 '10 at 15:33
@whuber I kept my answer pretty software-neutral. It's good to have the Excel implementation. –  Jeromy Anglim Oct 21 '10 at 5:03
@Jeromy I see what you mean. I was responding to your comment in answer to Alex's question. Of greater interest to me is the idea that Excel can be used as a prototype for commands in R, Mathematica, (and even APL if anyone remembers it), provided you use its array-oriented procedures. Excel itself is pretty bad as a statistical tool but good for rapid prototyping because it's easy to see and correct one's mistakes. –  whuber Oct 21 '10 at 5:09
show 1 more comment