turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Discussions
- :
- Total instances versus subset of instances -- Graph

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Jul 21, 2012 5:25 AM
(1228 views)

I'm assuming this question has been answered before but I can't seem to pin it down.

I'm trying to create a graph like this:

My data table looks like something like this with 1,000's of rows.....

I'd like to accumulate the number of Issues (entries in the datatable) on a per day basis over a 30 day period.... and compare it to the number of 'closed' issues over that same period.

So my plan is to generate a 'date' column with 30 days (rows) starting from today's date and count backward 30 day

SToday = today(); | |

csdate = format(SToday, "m/d/y"); | |

cedate = "06/21/2012"; // this is arbitrary --- I'm still trying to figure out how to obtain the start date ---- subtract 30 days from today's date??? |

sdate = Informat( csdate, "m/d/y" ); | |

edate = Informat( cedate, "m/d/y" ); |

dt << New Column( "Sequential Dates",

Numeric,

Continuous,

Format( "m/d/y", 12 ),

);

::i=1;

for each row(

For(i=1, i<30, i++, :Sequential Dates[] = Sequence( sdate, edate, In Days( -1 ), 1 ) ));

Then I was planning to create a column that would count each issue (entry in the datatable) that occurred on particular dates. So if there were 5 entries on 06/12/2012 there would be a '6' in the 'Found' column.

Then I need a column that accumulates the entries overtime --- so I would add previous row in the 'Found' column to the current row value.... and enter in a new column 'Found_accum'

For the 'closed' items I would do the same as above to create a 'Closed_accum' column

The graph with the 30 days on the x-axis and a line plot for the 'Found' and another line plot for the 'Closed'...

After spending hours trying to get this to work I think I'm on the wrong track and there may be a much simpler approach..... help.....

4 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

For the date arithmetic, you might look into the Date Increment and Date Difference functions. E.g.,

Date Increment(today(), "day", -30)

For calculating the cumulative Open/Found cases, I'd try creating a summary table to summarize N(Open) and N(Closed) by date, and then compute N(Found) = N(Open) + N(Closed). Finally, I'd add formula columns to convert those counts into cumulative counts. Something like:

:nopen + If( Row() == 1, 0, Lag(:nopen))

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Datetime values are stored and computed as a number of seconds since midnight, January 1, 1904. Since dates are just numbers you can add or subtract them. To get "day units", multiply by the number of seconds in a day (24 * 3600).

For example:

csdate = Today**()**;

for **(**i = **1**, i <= **30**, i++,

cedate = format**((**csdate - **(**i - **1)** * **24** * **3600)**, "m/d/y"**)**;

print**(**cedate**)**;

**)**;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

PMroz, Thanks for the data math ---

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Xan,

Thanks ---- you confirmed my suspicion that this capability was not 'built in' and the data has to be generated to create the graph.....

and thanks for the plan of attack/tasks to make this happen -----