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",
Format( "m/d/y", 12 ),
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.....
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))
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).
csdate = Today();
for (i = 1, i <= 30, i++,
cedate = format((csdate - (i - 1) * 24 * 3600), "m/d/y");
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 -----