Help with chart

[John]

I have a table with amongst others 2 fields: DateIn and DateOut. The idea is
to make a chart that shows the average number of days it takes for a request
to be answered. I'm planning to make a linechart with on the x-axis the
months (starting with the current month and ending 11 months later) and on
the y-axis the average number of days (DateOut minus DateIn). So the chart
will show the average number of days per month.

How would I go about this?
Which outputfields do I need in my table/query for my chart?
It's possible that there are 100 records for January and just 20 for
February. Is there a way to make that visible in the same chart?

Thanks in advance for any help.
john

[Bob Quintal]

"John" <[e-mail] in
news:f4***********@textnews.wanadoo.nl:

I have a table with amongst others 2 fields: DateIn and
DateOut. The idea is to make a chart that shows the average
number of days it takes for a request to be answered. I'm
planning to make a linechart with on the x-axis the months
(starting with the current month and ending 11 months later)
and on the y-axis the average number of days (DateOut minus
DateIn). So the chart will show the average number of days per
month.

How would I go about this?
Which outputfields do I need in my table/query for my chart?
It's possible that there are 100 records for January and just
20 for February. Is there a way to make that visible in the
same chart?

Thanks in advance for any help.
john

Some general responses to your info.
1) Do you mean starting with the current month and ending 11
months earlier? It's hard to predict the future.
2) if there are 100 records in January the average will be a
single number, same as for 20 rows in February.

Now some practtical information.
Create a query with year(DateIn)*100+month(datein)as a column.
and datediff("d",datein,nz(dateout,date()) as a second column.
the third column would contain any field which would have non-
blanks. Turn this into a Summary query, group by the first
field, average the second, and count the third.
You can also add a criteria to the first column with the
expression (year(date()-1)*100 +month(date()

This will give you 12 rows, with the period, the average
duration and the number of rows making that average.

Plot the period on the X axis, and either or both other columns
on the Y azis.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[John]

Thanks Bob,
See inline:

Some general responses to your info.
1) Do you mean starting with the current month and ending 11
months earlier? It's hard to predict the future.

:-) You're right, that was what I meant.

2) if there are 100 records in January the average will be a
single number, same as for 20 rows in February.

True, but the number of records involved per month would help to interpret
the figures. For instance, it could indicate that a month with a great less
number of requests, has a lower duration average. If the number of requests
for 2 particular months are the same but there is a big difference between
the average duration times of those months, that would be something to look
into.

Any idea on how to incorporate that into the chart?

Now some practtical information.
Create a query with year(DateIn)*100+month(datein)as a column.
and datediff("d",datein,nz(dateout,date()) as a second column.
the third column would contain any field which would have non-
blanks. Turn this into a Summary query, group by the first
field, average the second, and count the third.
You can also add a criteria to the first column with the
expression (year(date()-1)*100 +month(date()

This will give you 12 rows, with the period, the average

Great! thanks,
john
"Bob Quintal" <rq******@sPAmpatico.caschreef in bericht
news:Xn**********************@66.150.105.47...

"John" <[e-mail] in
news:f4***********@textnews.wanadoo.nl:

>I have a table with amongst others 2 fields: DateIn and
DateOut. The idea is to make a chart that shows the average
number of days it takes for a request to be answered. I'm
planning to make a linechart with on the x-axis the months
(starting with the current month and ending 11 months later)
and on the y-axis the average number of days (DateOut minus
DateIn). So the chart will show the average number of days per
month.

How would I go about this?
Which outputfields do I need in my table/query for my chart?
It's possible that there are 100 records for January and just
20 for February. Is there a way to make that visible in the
same chart?

Thanks in advance for any help.
john

Some general responses to your info.
1) Do you mean starting with the current month and ending 11
months earlier? It's hard to predict the future.
2) if there are 100 records in January the average will be a
single number, same as for 20 rows in February.

Now some practtical information.
Create a query with year(DateIn)*100+month(datein)as a column.
and datediff("d",datein,nz(dateout,date()) as a second column.
the third column would contain any field which would have non-
blanks. Turn this into a Summary query, group by the first
field, average the second, and count the third.
You can also add a criteria to the first column with the
expression (year(date()-1)*100 +month(date()

This will give you 12 rows, with the period, the average
duration and the number of rows making that average.

Plot the period on the X axis, and either or both other columns
on the Y azis.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[John]

I managed to add the number of requests to the month by basing another query
on the first query, so I can use that in the y-axis. I used this expression:

test: Str([expr1])+" ("+Str([AantalVanID_verzoek])+")"

It works but I get an extra space after the first bracket. It looks like
this:
200701 ( 9)
200702 ( 8)

Where does this space come from?
john

"John" <[e-mail] in bericht
news:f4**********@textnews.wanadoo.nl...

>
Thanks Bob,
See inline:

>Some general responses to your info.
1) Do you mean starting with the current month and ending 11
months earlier? It's hard to predict the future.

:-) You're right, that was what I meant.

>2) if there are 100 records in January the average will be a
single number, same as for 20 rows in February.

True, but the number of records involved per month would help to interpret
the figures. For instance, it could indicate that a month with a great
less number of requests, has a lower duration average. If the number of
requests for 2 particular months are the same but there is a big
difference between the average duration times of those months, that would
be something to look into.

Any idea on how to incorporate that into the chart?

>Now some practtical information.
Create a query with year(DateIn)*100+month(datein)as a column.
and datediff("d",datein,nz(dateout,date()) as a second column.
the third column would contain any field which would have non-
blanks. Turn this into a Summary query, group by the first
field, average the second, and count the third.
You can also add a criteria to the first column with the
expression (year(date()-1)*100 +month(date()

This will give you 12 rows, with the period, the average

Great! thanks,
john
"Bob Quintal" <rq******@sPAmpatico.caschreef in bericht
news:Xn**********************@66.150.105.47...

>"John" <[e-mail] in
news:f4***********@textnews.wanadoo.nl:

>>I have a table with amongst others 2 fields: DateIn and
DateOut. The idea is to make a chart that shows the average
number of days it takes for a request to be answered. I'm
planning to make a linechart with on the x-axis the months
(starting with the current month and ending 11 months later)
and on the y-axis the average number of days (DateOut minus
DateIn). So the chart will show the average number of days per
month.

How would I go about this?
Which outputfields do I need in my table/query for my chart?
It's possible that there are 100 records for January and just
20 for February. Is there a way to make that visible in the
same chart?

Thanks in advance for any help.
john

Some general responses to your info.
1) Do you mean starting with the current month and ending 11
months earlier? It's hard to predict the future.
2) if there are 100 records in January the average will be a
single number, same as for 20 rows in February.

Now some practtical information.
Create a query with year(DateIn)*100+month(datein)as a column.
and datediff("d",datein,nz(dateout,date()) as a second column.
the third column would contain any field which would have non-
blanks. Turn this into a Summary query, group by the first
field, average the second, and count the third.
You can also add a criteria to the first column with the
expression (year(date()-1)*100 +month(date()

This will give you 12 rows, with the period, the average
duration and the number of rows making that average.

Plot the period on the X axis, and either or both other columns
on the Y azis.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Bob Quintal]

"John" <[e-mail] in
news:f4**********@textnews.wanadoo.nl:

I managed to add the number of requests to the month by basing
another query on the first query, so I can use that in the
y-axis. I used this expression:

test: Str([expr1])+" ("+Str([AantalVanID_verzoek])+")"

It works but I get an extra space after the first bracket. It
looks like this:
200701 ( 9)
200702 ( 8)

Where does this space come from?
john

"John" <[e-mail] in bericht
news:f4**********@textnews.wanadoo.nl...

>>

the str() function always puts a leading space
To eliminate it use either the format() function

format([AantalVanID_verzoek],"0") see the help on format()

or the concatenation operator.

Str([expr1]) & " (" & [AantalVanID_verzoek] & ")"
--
Posted via a free Usenet account from http://www.teranews.com

[John]

Thank you.
john

"Bob Quintal" <rq******@sPAmpatico.caschreef in bericht
news:Xn**********************@66.150.105.47...

"John" <[e-mail] in
news:f4**********@textnews.wanadoo.nl:

>I managed to add the number of requests to the month by basing
another query on the first query, so I can use that in the
y-axis. I used this expression:

test: Str([expr1])+" ("+Str([AantalVanID_verzoek])+")"

It works but I get an extra space after the first bracket. It
looks like this:
200701 ( 9)
200702 ( 8)

Where does this space come from?
john

"John" <[e-mail] in bericht
news:f4**********@textnews.wanadoo.nl...

>>>

the str() function always puts a leading space
To eliminate it use either the format() function

format([AantalVanID_verzoek],"0") see the help on format()

or the concatenation operator.

Str([expr1]) & " (" & [AantalVanID_verzoek] & ")"
--
Posted via a free Usenet account from http://www.teranews.com

[Tim Marshall]

John wrote:

number of requests, has a lower duration average. If the number of requests
for 2 particular months are the same but there is a big difference between
the average duration times of those months, that would be something to look
into.

Any idea on how to incorporate that into the chart?

Hi John,

I would prefer to present this in two charts. One for average duration
and then another for number of requests.

I deal a lot with charts and too much information in one chart is just
overwhelming. My idea of the purpose of a chart is that it jumps out
and screams "LOOK AT <whatever concept I'm trying to portray>!!!!".
It's harder to do this with too much information.

Besides, if you look at MS Chart, there are TWO major things it can
portray: a single VALUE (either duration average or number of requests)
broken down by some GROUPING (month). You can also further classify the
grouping (in your case, possibly by year/fiscal year) - -this is known
as a series.

If you stick to the above concept, you'll avoid a lot of brain ache. 8)
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me

[John]

Thanks for the insight Tim.
I will do so.
john

"Tim Marshall" <TI****@PurplePandaChasers.Moertheriumschreef in bericht
news:f4**********@coranto.ucs.mun.ca...

John wrote:

>number of requests, has a lower duration average. If the number of
requests for 2 particular months are the same but there is a big
difference between the average duration times of those months, that would
be something to look into.

Any idea on how to incorporate that into the chart?

Hi John,

I would prefer to present this in two charts. One for average duration
and then another for number of requests.

I deal a lot with charts and too much information in one chart is just
overwhelming. My idea of the purpose of a chart is that it jumps out and
screams "LOOK AT <whatever concept I'm trying to portray>!!!!". It's
harder to do this with too much information.

Besides, if you look at MS Chart, there are TWO major things it can
portray: a single VALUE (either duration average or number of requests)
broken down by some GROUPING (month). You can also further classify the
grouping (in your case, possibly by year/fiscal year) - -this is known as
a series.

If you stick to the above concept, you'll avoid a lot of brain ache. 8)
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me