Show sales by month yearly in columnchart

Identity Works commented on Mar 12, 2020

Hi,

I was wondering if there is a neat way to show sales by month in the columnchart while showing yearly comparison.

so my sql would get the data like this :

SELECT YEAR,Month,Sale FROM sales GROUP BY YEAR,Month ORDER BY YEAR,Month;

say the output is sales for the years 2019 and 2020 by month, the columnchart would show sales for all the months for 2019 and then till march for 2020 in the same chart.

Identity Works commented on Mar 13, 2020

Hi guys,

Any luck with this?

Thanks.

KoolReport commented on Mar 14, 2020

Hi, would you mind to sketch a simple column chart that you need.

Identity Works commented on Mar 17, 2020

David Winterburn commented on Mar 18, 2020

Hi,

This is a perfect case for using the Cube process. Just set your sql query like this:

//MyReport.php
$this->src("mySource")
->query("SELECT YEAR,Month,Sale FROM sales")
->pipe(new \koolreport\cube\processes\Cube(array(
    "row" => "Month",
    "column" => "YEAR",
    "sum" => "Sale"
)))
->pipe($this->dataStore("myDatastore");

//MyReport.view.php
\koolreport\widgets\google\ColumnChart::create(array(
    "dataSource" => $this->dataStore("myDatastore"),
    ...
));

Please try this and let us know if you have any question. Thanks!

Identity Works commented on Mar 18, 2020

Hi ,

Thanks for that solution, works perfectly, but it generates a column called {{all}} beside the month/year values. so there are 3 bars instead of 2 now. is there a way to remove or hide the {{all}} bar?

Thanks.

David Winterburn commented on Mar 19, 2020

RemoveColumn process to your rescue:

https://www.koolreport.com/docs/processes/removecolumn/

KoolReport's Forum

Show sales by month yearly in columnchart #1346

Build Your Excellent Data Report