5307_en-US

liliconcom

Total instances versus subset of instances -- Graph<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

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.....

Re: Total instances versus subset of instances -- Graph

PMroz,    Thanks for the data math ---

Re: Total instances versus subset of instances -- Graph

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 -----   

Re: Total instances versus subset of instances -- Graph

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);

);

Re: Total instances versus subset of instances -- Graph

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))