Calculate time difference and fill a graph with it

Hi, 
 
I have a database table with lots of orders in it. They are in different statuses, every status has another date. I would like to calculate the time between the creation (1) and the delivery (5). The table looks like this 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
ID STATUS DATE
112 1 01-01-2010
112 5 10-01-2010
190 1 07-01-2010
190 5 13-01-2010
234 1 02-02-2010
234 5 09-02-2010
...
...
</pre>
 
I would like to have the average processingtime for all orders from status 1 to 5 for each month, to put it in a graph to have a monthly overview of the average processing time. 
 
I'm just not sure how to achive this, I hope somebody has an idea. Thanks for every hint. 
 
Regards, 
Sebastian

Find more posts tagged with

Comments

mwilliams

Hi Sebastian,

Will you data always be sorted this way? With the like IDs right next to each other with 1 first, then 5? Also, how would you like to handle items that are in stage one in say Feb, but don't reach stage 5 til March? Which month's average would that fall into? The origination date or the delivered date?

I would think you could use a couple temp variable you define in your initialize method to hold the last line values and in a computed column you could figure the time difference between the two entries for each ID. You could then make a chart based on the new computed column. You'll have to filter out the first line for each ID in the chart because you'll have no computation in those lines, so if you're wanting to use the origination date, you'll have to create another computed column to have that value. Hope this helps. Let me know if you have questions.

basti

Well, i've just figured out I forgott something: Every order has a storage id to. Basically I dont wanna break it down by month, just the overall average time per storage. 
 
So the graph just needs to show the average time (horizontal bar chart) each storage. 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>ID STATUS DATE STORAGE
112 1 01-01-2010 3
112 5 10-01-2010 3
190 1 07-01-2010 1
190 5 13-01-2010 1
234 1 02-02-2010 4
234 5 09-02-2010 4
...
...</pre>
 
How could I forget that, sorry. 
 
Maybe you can gimme some function names and hints, I've never really did scripting with BIRT so I'm gonna need to google for every small thing I need to realize that. 
 
Thanks and best regards, 
Sebastian

basti

Just figured an easy solution. I just compute a new column with a script in the Data Set.

The table is sorted by id. So in the first iteration I just save the currents row data, in the second iteration I check if the id is the same, if yes: calculate the difference in day and thats it. Afterwards I filter for every non 0 day (second iteration) row.

That's it. Thanks :-)

But one problem still persists, the bar chart doesn't give me the average delivery time (in days). The "Y-Series" is ment to have 1, 2, 3..... X in days. But as soon as I aggregate on average the axis changes to 4, 4, 4, 4, 5, 5, 5, 5. Why's so? I dont know whats the matter.

Any hints?

[edit]
I just attached a screenshot, the values on top of the bar are correct, but the axis on the left is wrong! I'm confused.

mwilliams

Sebastian,

Can you create a quick example of what you're doing using a flat file dataSet and attach it in here so I can run it? Also, what is your BIRT version?