increment if a hike falls within that group..
<cfparam name="group1" default="0">
<cfparam name="group2" default="0">
<cfparam name="group3" default="0">
<cfparam name="hikeGrouping" default="500">
<cfoutput query="queryOfHikes">
<cfif hikeHeight LT hikeGrouping>
<cfset group1 = group1 + 1>
<cfelseif hikeHeight LT evaluate(hikeGrouping * 2)>
<cfset group2 = group2 + 1>
</cfif>
</cfoutput>
This should give you values for your groups at the end for how many hikes
fell into that group. Since CFIF evaluates in order, just see if the hike
is less than that amount and if so, it falls into that group. Use
hikeGrouping to set the size of the groups. Then, later, when you climb
Everest, you can change it to 1000 so you don't have too many pie pieces.
John Burns
-----Original Message-----
From: John Munyan [mailto:[EMAIL PROTECTED]
Sent: Friday, November 14, 2003 11:51 AM
To: CF-Talk
Subject: RE: Question from a beginner
The link is http://www.attrition.ws/hike.cfm (click piechart).
Basically the hikechart.cfm page takes in a url variable from 1-6 to set
some values and based on the input populates the #chartme# variable to
build the query. I am using mssql for this. I am trying to take into
consideration the discreet values for Elevation, Time, and distance so
the count value from the sql query would be categorized. Currently each
discreet value is charted. See how many values there are for elevation?
http://www.attrition.ws/hikechart.cfm?Chart=6
<http://www.attrition.ws/hikechart.cfm?Chart=6&Totpics=12&Columns=3&Condi>
&Totpics=12&Columns=3&Condi
tion=&UrlValue=
When drilling into the chart I pass back a couple url variables to
refine the query to the hike.cfm page, which is why I am trying to
handle this logic in sql rather than the application per se so that I
can pass this back to the refining page. I was hoping that based in
the incoming url.chart value I could build some conditional logic within
the sql query to group Elevation etc into categories of 500. Any
ideas? Below is the relevant hikechart.cfm code:
<cfif IsDefined("URL.Chart") AND url.Chart EQ "1">
<cfset ChartMe="Region">
<cfset Title="Regional Distribution of Hikes">
</cfif>
<cfif IsDefined("URL.Chart") AND url.Chart EQ "2">
<cfset ChartMe="HikeDifficulty">
<cfset Title="Difficulty PieChart">
</cfif>
<cfif IsDefined("URL.Chart") AND url.Chart EQ "3">
<cfset ChartMe="Reccomend">
<cfset Title="Reccomendation Piechart">
</cfif>
<cfif IsDefined("URL.Chart") AND url.Chart EQ "4">
<cfset ChartMe="HikeTime">
<cfset Title="Round Trip Time In Hours PieChart">
</cfif>
<cfif IsDefined("URL.Chart") AND url.Chart EQ "5">
<cfset ChartMe="RoundTripDistance">
<cfset Title="Round Trip Distance In Miles PieChart">
</cfif>
<cfif IsDefined("URL.Chart") AND url.Chart EQ "6">
<cfset ChartMe="ElevationGain">
<cfset Title="Elevation Gained In Feet PieChart">
</cfif>
<!--- End Catch block for URL variables --->
<cfquery name="Recordset1" datasource="hike">
SELECT #ChartMe#, count(*) as
Chart_count
FROM hike
Where #ChartMe# is not null
group by #ChartMe#
order by #ChartMe#;
</cfquery>
<cfchart showborder="yes" show3d="yes" chartwidth="600"
chartheight="400" pieslicestyle="sliced"
url=""> umns#&urlvalue=$ITEMLABEL$">
<cfchartseries type="pie" query="Recordset1" itemcolumn="#ChartMe#"
valuecolumn="Chart_count">
</cfchartseries>
</cfchart>
Thanks,
John
-----Original Message-----
From: Jerry Johnson [mailto:[EMAIL PROTECTED]
Sent: Friday, November 14, 2003 8:27 AM
To: CF-Talk
Subject: Re: Question from a beginner
John,
Are your elevation value stored in feet units?
If so, you can set a new variable:
<cfset chart_me_calc="integer(elevation/500)">
(the actual syntax may be different for your db.)
Then:
<cfset chart_me="elevation">
<cfparam name="chart_me_code" default="#chart_me#">
select #chart_me_calc# as #chart_me#, count(*) as Chart_count
Post the url when you are done so we can check it out.
Jerry Johnson
>>> [EMAIL PROTECTED] 11/14/03 11:04AM >>>
I have a website describing hikes I have taken. I have recently added a
charting page to chart much of the data I collect such as Region,
Recommendation, Difficulty which works fine (since these values are
essentially categorical ones with few values 5-6). However I also am
trying to chart Time, Elevation gain, and Distance hiked. Elevation
Gain in particular charts poorly as hardly any of the 60 hikes had the
same gain.
What I would like to do is when I am charting Time, Elevation gain, and
Distance I would like to categorized the output into just a few. For
Elevation it would probably be best to use 500ft increments. 0-500,
500-1000, 1000-1500 and so on.
The current query I am using for the cfchart call is:
SELECT #ChartMe#, count(*) as
Chart_count
FROM hike
Where #ChartMe# is not null
group by #ChartMe#
order by #ChartMe#;
Does anyone have an idea of how this query might be amended or otherwise
rewritten to allow the above categorization to happen rather than just
counting discreet instances? I have tried searching the web for quite a
while to no avail, and was thinking this group might have some clues.
Even good sql sites, groups, or book recommendations would be very
welcomed.
Thanks sincerely,
John
________________________________
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
