What is the best way to create a Trend report?

[montypython1]

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").

[indent]select dealership, dealdate, grosspybl from dmssale order by dealdate desc ;[/indent]

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

 

[ChrisHirst]

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-functions-and-modifiers.html

and

https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html)

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

 

[montypython1]

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

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[montypython1]

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

 

[montypython1]

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

 

[ChrisHirst]

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

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[montypython1]

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

 

[SkipVought]

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...

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

 from dmssale 

Group By dealership, Dte

Order By Dte

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[montypython1]

Thanks Skip,

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

Have a great day,
Dave