INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

What is the best way to create a Trend report?

What is the best way to create a Trend report?

(OP)
Greetings,

What is the best way to create a Trend report using MySql? The following code lists all the individual records, but I would like to total and group the gross payable (using field "grosspybl") amounts within each of the most recent 25 months (based on field "dealdate").

CODE -->

select dealership, dealdate, grosspybl from dmssale order by dealdate desc ;

Does it make sense to create 25 new fields called something like "grosspybl_01, grosspybl_02, grosspybl_03, grosspybl_04, ... grosspybl_25" to represent the sale transactions that occurred 1 month back, or 2 months back, ... or 25 months back?
Any suggestions?

Thanks,
Dave

RE: What is the best way to create a Trend report?

Quote:

Does it make sense to create 25 new fields called something like "grosspybl_01, grosspybl_02, grosspybl_03, grosspybl_04, ... grosspybl_25" to represent the sale transactions that occurred 1 month back, or 2 months back, ... or 25 months back?

ABSOLUTELY NOT! that would just add unnecessary overhead


You can create "GROUP BY" (http://dev.mysql.com/doc/refman/5.7/en/group-by-fu... and https://dev.mysql.com/doc/refman/5.7/en/group-by-h...) queries to aggregate records in the rows that are selected, also MySQL has built in aggregate functions for statistical analysis.
COUNT(field), SUM(field), AVG(field), MIN(field), MAX(field), STD(field)


So to find the highest gross for any period could be SELECT MAX(grosspybl) FROM .... WHERE [date criteria];

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

RE: What is the best way to create a Trend report?

(OP)
Hi Chris,

Thanks for the reply, but I do not want to calculate the MAX() of anything. I am trying to total up all the amounts within each month (for the most recent 25 separate months). I would then like to be able to display this info on a trend-line showing 25 separate data points (2 year trend plus the current month = 25).

If March 2016 has 5 grosspybl transactions of 10,20,30,40,50, then I want to store 150 as the total grosspybl for March 2016. I would need to do the same calc for the next 24 months. After calculating all 25 months, I would then be able to display a trend-line showing 25 separate data-points (or export these data-points so they could be graphed within Excel).

Thanks,
Dave

RE: What is the best way to create a Trend report?

You don't need to "store" anything! These are aggregations that can be made at any time.

Such can be very simply done totally within Excel.

You can query your db from Excel via Data > Get external data > from other sources... and make a connection to your SQL db and perform an appropriate query. If you just returned raw data, the aggregation(s) could be done in a PivotTable. Then chart the results.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: What is the best way to create a Trend report?

(OP)
Great idea! Never thought about handling it entirely within Excel.

But if I also wanted to create the data points within the program (and not use Excel), how would I best do this?

Thanks,
Dave

RE: What is the best way to create a Trend report?

(OP)
I'd like to display the result as a Trend in a browser, rather than have to open Excel.
And I'd like the output to look something like this:

Dealership#1 ... GrossPybl_2016_03 ... GrossPybl_2016_02 ... GrossPybl_2016_01 ... GrossPybl_2015_12 ... GrossPybl_2015_11 etc
Dealership#2 ... GrossPybl_2016_03 ... GrossPybl_2016_02 ... GrossPybl_2016_01 ... GrossPybl_2015_12 ... GrossPybl_2015_11 etc
Dealership#3 ... GrossPybl_2016_03 ... GrossPybl_2016_02 ... GrossPybl_2016_01 ... GrossPybl_2015_12 ... GrossPybl_2015_11 etc
Dealership#4 ... GrossPybl_2016_03 ... GrossPybl_2016_02 ... GrossPybl_2016_01 ... GrossPybl_2015_12 ... GrossPybl_2015_11 etc

Thanks,
Dave

RE: What is the best way to create a Trend report?

Quote:

I'd like to display the result as a Trend in a browser, rather than have to open Excel.

You can't do that in MySQL.

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

RE: What is the best way to create a Trend report?

And a tabular report often fails to tell the story you wish to communicate while a chart can do a better job. The more data points your data has, the more a chart paints a picture that users can quickly assimilate.

So, from an application like Excel you can query sundry data bases, massage and analyze the data and report in a number of diverse ways.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: What is the best way to create a Trend report?

(OP)
I agree. I just want to use MySql to create these data points, and then use HTML (rather than Excel) to display and graph this data.

Dave

RE: What is the best way to create a Trend report?

BTW, Mar 2016 is not a date value. Can't be collated as a date.

Convert dealdate to (yyyy, mm, 1) or yyyy_mm so it will collate.

SUM(grosspybl)

I'm not a MySQL user, so I can't give you the correct syntax...

CODE

select 
  dealership
, Year(dealdate) ||'_'|| Month(dealdate) As Dte
, SUM(grosspybl)

 from dmssale 

Group By dealership, Dte

Order By Dte 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: What is the best way to create a Trend report?

(OP)
Thanks Skip,

I'll give your code a try ... It sounds like what I'm looking at.

Have a great day,
Dave

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close