Chart: Expression in query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I am trying to have a chart report displaying the results of following query
on which the chart is based on:

Fields used for the Total query are:

-CountOfDateOfReply: Displays the number of dates when replies were done on
time
-CountOfDateLoggedIn: Displays the number of dates when the data was first
entered into dbse.
-Following Expression:
Stage1KPINumber:Count([QryKPIStage1AOnTimeUnion].[DateOfReply]/[QryKPIStage1A].[DateLoggedIn])

The first 2 fields return the right value:

CountOfDateOfReply: 3
CountOfDateLoggedIn: 11

The result number in the Expression field comes up with 3, which is wrong,
when I change the propriety of this expression to percent, I then get 300%,
which is totally way out, the percentage should be 27%.

I also want to display the DateLoggedIn in my chart as date not as a count
digit from query, how can I achieve this?

Is there something I am doing wrong? How could I fix this?
Any help will be greatly appreciated.
Chris.
 
"Chris"wrote:
I am trying to have a chart report displaying the results of following
query
on which the chart is based on:

Fields used for the Total query are:

-CountOfDateOfReply: Displays the number of dates when replies were done
on
time
-CountOfDateLoggedIn: Displays the number of dates when the data was first
entered into dbse.
-Following Expression:
Stage1KPINumber:Count([QryKPIStage1AOnTimeUnion].[DateOfReply]/[QryKPIStage1A].[DateLoggedIn])

The first 2 fields return the right value:

CountOfDateOfReply: 3
CountOfDateLoggedIn: 11

The result number in the Expression field comes up with 3, which is wrong,
when I change the propriety of this expression to percent, I then get
300%,
which is totally way out, the percentage should be 27%.

I also want to display the DateLoggedIn in my chart as date not as a count
digit from query, how can I achieve this?

Is there something I am doing wrong? How could I fix this?
Any help will be greatly appreciated.
Click to expand...
Hi Chris,

First, I wonder if your expression needs to be
division of 2 counts?

Stage1KPINumber:
Count([QryKPIStage1AOnTimeUnion].[DateOfReply])/
Count([QryKPIStage1A].[DateLoggedIn])

I don't understand final question. If you want individual
login dates, you will lose your grouping

but, might be easier to diagnose if you provided
the full SQL of your query (and probably table
structure/ data example/ query result expected
from data example)...

good luck,

gary
 
Hi Gary,

I have made the change on my expression and it did work, so thank you for
this!

I understand what you mean about loosing my groupings with the date
displaying. What I am looking for is a chart that will display the percentage
per month ie the 27% that I have now relates to the period Septembre 07, I
would like to see this on the axis of my chart: ie Sep-07 : 27%; Oct-07:
35%....
Chris.

Gary Walter said:
"Chris"wrote:
I am trying to have a chart report displaying the results of following
query
on which the chart is based on:

Fields used for the Total query are:

-CountOfDateOfReply: Displays the number of dates when replies were done
on
time
-CountOfDateLoggedIn: Displays the number of dates when the data was first
entered into dbse.
-Following Expression:
Stage1KPINumber:Count([QryKPIStage1AOnTimeUnion].[DateOfReply]/[QryKPIStage1A].[DateLoggedIn])

The first 2 fields return the right value:

CountOfDateOfReply: 3
CountOfDateLoggedIn: 11

The result number in the Expression field comes up with 3, which is wrong,
when I change the propriety of this expression to percent, I then get
300%,
which is totally way out, the percentage should be 27%.

I also want to display the DateLoggedIn in my chart as date not as a count
digit from query, how can I achieve this?

Is there something I am doing wrong? How could I fix this?
Any help will be greatly appreciated.
Click to expand...
Hi Chris,

First, I wonder if your expression needs to be
division of 2 counts?

Stage1KPINumber:
Count([QryKPIStage1AOnTimeUnion].[DateOfReply])/
Count([QryKPIStage1A].[DateLoggedIn])

I don't understand final question. If you want individual
login dates, you will lose your grouping

but, might be easier to diagnose if you provided
the full SQL of your query (and probably table
structure/ data example/ query result expected
from data example)...

good luck,

gary
Click to expand...
 
Hi Chris,

It sounds like you want to also group by

Format([DateLoggedIn], "mmm-yy")

Or...I could have completely misunderstood...

good luck,

gary

Chris said:
I have made the change on my expression and it did work, so thank you for
this!

I understand what you mean about loosing my groupings with the date
displaying. What I am looking for is a chart that will display the
percentage
per month ie the 27% that I have now relates to the period Septembre 07, I
would like to see this on the axis of my chart: ie Sep-07 : 27%; Oct-07:
35%....
Chris.

Gary Walter said:
"Chris"wrote:
I am trying to have a chart report displaying the results of following
query
on which the chart is based on:

Fields used for the Total query are:

-CountOfDateOfReply: Displays the number of dates when replies were
done
on
time
-CountOfDateLoggedIn: Displays the number of dates when the data was
first
entered into dbse.
-Following Expression:
Stage1KPINumber:Count([QryKPIStage1AOnTimeUnion].[DateOfReply]/[QryKPIStage1A].[DateLoggedIn])

The first 2 fields return the right value:

CountOfDateOfReply: 3
CountOfDateLoggedIn: 11

The result number in the Expression field comes up with 3, which is
wrong,
when I change the propriety of this expression to percent, I then get
300%,
which is totally way out, the percentage should be 27%.

I also want to display the DateLoggedIn in my chart as date not as a
count
digit from query, how can I achieve this?

Is there something I am doing wrong? How could I fix this?
Any help will be greatly appreciated.
Click to expand...
Hi Chris,

First, I wonder if your expression needs to be
division of 2 counts?

Stage1KPINumber:
Count([QryKPIStage1AOnTimeUnion].[DateOfReply])/
Count([QryKPIStage1A].[DateLoggedIn])

I don't understand final question. If you want individual
login dates, you will lose your grouping

but, might be easier to diagnose if you provided
the full SQL of your query (and probably table
structure/ data example/ query result expected
from data example)...

good luck,

gary
Click to expand...
Click to expand...
 
Hi Gary,

This is exactly what I was looking for! Thank you so much for your time.
Chris.

Gary Walter said:
Hi Chris,

It sounds like you want to also group by

Format([DateLoggedIn], "mmm-yy")

Or...I could have completely misunderstood...

good luck,

gary

Chris said:
I have made the change on my expression and it did work, so thank you for
this!

I understand what you mean about loosing my groupings with the date
displaying. What I am looking for is a chart that will display the
percentage
per month ie the 27% that I have now relates to the period Septembre 07, I
would like to see this on the axis of my chart: ie Sep-07 : 27%; Oct-07:
35%....
Chris.

Gary Walter said:
"Chris"wrote:

I am trying to have a chart report displaying the results of following
query
on which the chart is based on:

Fields used for the Total query are:

-CountOfDateOfReply: Displays the number of dates when replies were
done
on
time
-CountOfDateLoggedIn: Displays the number of dates when the data was
first
entered into dbse.
-Following Expression:
Stage1KPINumber:Count([QryKPIStage1AOnTimeUnion].[DateOfReply]/[QryKPIStage1A].[DateLoggedIn])

The first 2 fields return the right value:

CountOfDateOfReply: 3
CountOfDateLoggedIn: 11

The result number in the Expression field comes up with 3, which is
wrong,
when I change the propriety of this expression to percent, I then get
300%,
which is totally way out, the percentage should be 27%.

I also want to display the DateLoggedIn in my chart as date not as a
count
digit from query, how can I achieve this?

Is there something I am doing wrong? How could I fix this?
Any help will be greatly appreciated.
Hi Chris,

First, I wonder if your expression needs to be
division of 2 counts?

Stage1KPINumber:
Count([QryKPIStage1AOnTimeUnion].[DateOfReply])/
Count([QryKPIStage1A].[DateLoggedIn])

I don't understand final question. If you want individual
login dates, you will lose your grouping

but, might be easier to diagnose if you provided
the full SQL of your query (and probably table
structure/ data example/ query result expected
from data example)...

good luck,

gary
Click to expand...
Click to expand...
Click to expand...
 
Back
Top