2. PHPRunner General questions
  3. [SOLVED] Compare Last Year vs. This Year in a Chart
This topic is locked
[SOLVED]

Compare Last Year vs. This Year in a Chart

 		10/18/2022 2:02:40 PM
PHPRunner General questions
D
 DealerModules authorDevClub member

Hello, looking for some help with formatting the mysql query to show a multiple year comparison on a chart.
Here is what I have:
My table just has three fields , monthly_salesID, last_date_in_month and sales_amount.
img alt

My query output looks like:
img alt

I would like to compare Last Year vs. This Year with the columns for each month side by side. This is my current chart:
img alt

And I found just an example from the net to show how Jan has 2 colored columns next to each other, followed by Feb, etc..
img alt

How do I format the MySQL to get multiple series for each year?

Thanks in advance for any help.

Paul
fhumanes 10/20/2022

Hello,

If you need to use all Anychart's power, I think an alternative is to use the "snippet" as defined by this article. https://fhumanes.com/blog/otros-ejemplos/uso-de-anychart-en-snippet/

Thus, you can consult the Anychart website and see and analyze all the possibilities that the product offers. https://www.anychart.com/products/anychart/overview/

It is necessary to program a little in PHP, but the result is exceptional.

Cheers,
fernando
Admin 10/20/2022

You need to modify your query the way so data for 2021 and 2022 appear as separate columns. I guess some sort of subquery will be required. Also instead of year_month you are going to need just month column, that would be your label.

This is not really a PHPRunner question and I guess you can have better like finding the solution on Google or Stackoverflow. I'm sure someone this kind of task before and published a solution.

D
 		DealerModules authorDevClub member 10/20/2022

Thanks. If i get it figured out, I will post my new query so maybe someone else doing a comparison chart can see what worked for me.
Thanks again.
D
 		DealerModules authorDevClub member 10/22/2022

So based on the comments from above and the need to be able to have each year in it's own series, I have changed the form for entering the data into the sale table.
After this change it was much easier for me to make a more appealing chart. (shown below) Thanks again for the input.
( I still am massaging the percentage number to to include one decimal place and alignment of the table below the chart, but well on the way to finishing it.)

img alt

Thanks again!
D
 		DealerModules authorDevClub member 10/22/2022

And the query that I ended up with was :

img alt

and the new form looks like:

img alt

M
 		Mark Kramer 10/23/2022

Would you kindly upload the sql code too please? I would like to see it for insperation.

Thank you.
D
 		DealerModules authorDevClub member 10/23/2022

Hi Mark,

No problem. From reading Fernando's and the Admin's comments. The two biggest factors is having one of the fields as 'Month' so it can compare the figures and having each yearly sales amount in it's own column (field) so you can use it as a series in the chart area. I added years in the table up to 2030 so I wouldn't have to mess with it for a while. In the add and edit forms, I turned on 'Inline Edit' and 'Inline Add' for ease of entering data.
img alt

The 'order' field I added only for sorting purposes, without it the chart was starting off as 'April' since ascending would put the A's first.

I would assume that you can set anything up in the series (fields) that you would want to track, ie Units, Net, Profit or a calculated fields of anyone of these.

Maybe a better solution out there but for me this was the easiest. ( The form widget to show and hide columns is great for input too. )

One other item that would be neat would be to have the ability to select the columns before rendering the chart so you can compare two or more selected years dynamically.

Right now I have built a chart comparing every two years as below until I come up with a dynamic fix for it.

img alt

Paul