Known Participant

Question

cfchart output by month

Forum|Forum|9 years ago
March 16, 2017
2 replies
745 views

I'm using CF10.

I want to output a count of events by the month they happened each.

Each month will always have at least 1 event, so I'm not worried about months with 0.

This is showing me the 12 months in order, but adding them up.

The query is retrieving records back through the last 3 years.

Where did I go wrong?

I want to show the months in order for the last three years.

<cfquery Name="OccOpenChart" datasource="#APPLICATION.DSN#">
SELECT
  COUNT(OccurrenceNumber) As OccTotal,
    year(DateofReport) as OccYear,
    month(DateofReport) as Occmonth
  FROM OccurrenceDateTracking
  WHERE Year(DateofReport) > #thisyear#
  GROUP BY year(DateofReport), month(DateofReport)
</cfquery>

<cfchart
  format="png"
  chartwidth="750"
  chartheight="500"
  showlegend="yes"
  xaxistitle="Year of Report"
  yaxistitle="Occurrence Count"
  show3d="no"
  tipstyle="none"
  fontsize="12">
      <cfchartseries
  type="bar"
  query="OccOpenChart"
  dataLabelStyle="value"
  itemcolumn="Occmonth"
  valuecolumn="OccTotal"
  serieslabel="Total" />

This topic has been closed for replies.

EddieLotter

Inspiring

bloodbanker wrote

I want to show the months in order for the last three years.

By this do you mean that you want to see the three Januaries first, then the three Februaries etc.?

If so, then you need to switch the expressions in your Group By clause.

Cheers

Eddie

B

bloodbankerAuthor

Known Participant

Sorry.

Let's say it was the last three calendar years.

I want to display jan - december of 2015 followed by jan-dec 2016 followed by jan - mar 2017.

EddieLotter

Inspiring

In that case try switching the expressions in your Group By clause and see if you get what you want. If not, try adding an Order By clause. Some database engines will allow an Order By clause that has a different column order from the Group By clause.

Cheers

Eddie

EddieLotter

Inspiring

Do the following and post the result:

<cfdump var="#OccOpenChart#">

Cheers

Eddie

B

bloodbankerAuthor

Known Participant

EddieLotter  wrote
Do the following and post the result:
<cfdump var="#OccOpenChart#">

Cheers
Eddie

query

RESULTSET

query
	OCCMONTH	OCCTOTAL	OCCYEAR
1	1	35	2015
2	1	53	2016
3	1	42	2017
4	2	28	2015
5	2	37	2016
6	2	43	2017
7	3	23	2015
8	3	37	2016
9	3	18	2017
10	4	28	2015
11	4	46	2016
12	5	34	2015
13	5	70	2016
14	6	32	2015
15	6	53	2016
16	7	29	2015
17	7	46	2016
18	8	36	2015
19	8	51	2016
20	9	40	2015
21	9	30	2016
22	10	34	2015
23	10	43	2016
24	11	40	2015
25	11	44	2016
26	12	34	2015
27	12	44	2016

CACHED

false

EXECUTIONTIME

15

SQL

SELECT COUNT(OccurrenceNumber) As OccTotal, year(DateofReport) as OccYear, month(DateofReport) as Occmonth FROM OccurrenceDateTracking WHERE Year(DateofReport) > 2014 GROUP BY year(DateofReport), month(DateofReport)

Here's a screenshot of the page

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded