Highlighted

cfchart output by month

Explorer ,
Mar 16, 2017

Copy link to clipboard

Copied

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" />

Views

337

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

cfchart output by month

Explorer ,
Mar 16, 2017

Copy link to clipboard

Copied

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" />

Views

338

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Mar 16, 2017 0
Advocate ,
Mar 17, 2017

Copy link to clipboard

Copied

Do the following and post the result:

<cfdump var="#OccOpenChart#">

Cheers

Eddie

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Mar 17, 2017 0
Explorer ,
Mar 17, 2017

Copy link to clipboard

Copied

EddieLotter  wrote

Do the following and post the result:

<cfdump var="#OccOpenChart#">

Cheers

Eddie

query
RESULTSET
query
OCCMONTHOCCTOTALOCCYEAR
11352015
21532016
31422017
42282015
52372016
62432017
73232015
83372016
93182017
104282015
114462016
125342015
135702016
146322015
156532016
167292015
177462016
188362015
198512016
209402015
219302016
2210342015
2310432016
2411402015
2511442016
2612342015
2712442016
CACHEDfalse
EXECUTIONTIME15
SQLSELECT 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

Occurrences.png

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Mar 17, 2017 0
Advocate ,
Mar 17, 2017

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Mar 17, 2017 0
Explorer ,
Mar 17, 2017

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Mar 17, 2017 0
Advocate ,
Mar 17, 2017

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Mar 17, 2017 0
Explorer ,
Mar 17, 2017

Copy link to clipboard

Copied

Thanks.  I tried switching the group by, added an order by, tried switching all of those around.

The dump shows what I want the chart to show, but the chart keeps displaying like it is in the pic.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Mar 17, 2017 0
Advocate ,
Mar 17, 2017

Copy link to clipboard

Copied

That doesn't seem possible. If you change the query then the chart has to change. If you get the same chart no matter what you do to the query then I suspect the chart is not using the query you think it is, or you are seeing some kind of caching issue.

When I use your data set I get a different chart:

attach2.pngattach3.pngattach1.png

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Mar 17, 2017 0
Advocate ,
Mar 17, 2017

Copy link to clipboard

Copied

Sorry, I meant to say "If not, then you need to switch the expressions in your Group By clause."

In other words, if you switch the expressions then you will get all months in the first year, followed by all months in the next year, and so on.

Cheers

Eddie

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Mar 17, 2017 0