Multiple columns ChartJS

arthur commented on Jul 30, 2018

Hi,

I would like to create a report like the first ChartJS example. Instead of product I would have sales people with number of sells, value etc.

However, what should I do in the processing file to display multiple column ? I can't use an array on the visualization like you did.

Thanks

KoolReport commented on Jul 30, 2018

The widget can take array or dataStore as dataSource. For the purpose of demonstrating, we use the array as dataSource butcan use dataStore like this "dataSource"=>$this->dataStore("mydata")

arthur commented on Jul 31, 2018

Hi, thanks.

But if I want to display, let say 2 columns. For exemple I want to display, for each salesmen, the number of sells and the value. Should I do like this ?

 ->pipe(new Group(array(
            "by"=>"salesman",
            "sum"=>"sales, amount_of_sales"
        )))
        ->pipe($this->dataStore('sales'));

KoolReport commented on Jul 31, 2018

It is correct to do so.

arthur commented on Jul 31, 2018

Thanks it works!

But is it possible to leverage the differences of scales ? Because the difference is huge (see below)

KoolReport commented on Jul 31, 2018

There is option to use logarithmic scale instead of linear? Do you mean to use that?

arthur commented on Jul 31, 2018

The number of sales is 25, and the value of sales is 8000. I would like the visual difference between this 2 numbers.

KoolReport commented on Jul 31, 2018

I see, chartJs does support multiple scales, for example

ColumnChart::create(array(
    "columns"=>array(
        "category",
        "salesNumber"=>array(
            ...
            "config"=>array(
                "yAxisID"=>"first"
            )
        ),
        "salesAmount"=>array(
            ...
            "config"=>array(
                "yAxisID"=>"second"
            )
        ),
    ),
    "options"=>array(
        "yAxes"=>array(
            array(
                "id"=>"first",
                "type"=>"linear",
                "position"=>"left",
            ),
            array(
                "id"=>"second",
                "type"=>"linear",
                "position"=>"right",
            ),
        )
    )
));

arthur commented on Jul 31, 2018

Thanks. Thbe code doesn't work because the graph doesn't appear anymore.

Here is my code :

ColumnChart::create(array(
                "title"=>"Sale Report",
                "dataStore"=>$this->dataStore('PO'),
                "columns"=>array(
                    "salesPerson",
                       "PO"=>array(
                       "type"=>"number", 
                       "label"=>"PO Marge Annuelle",
                       "config"=>array(
                           "yAxisID"=>"first",
                       )
                ),

                 "Qte"=>array(
                  "type"=>"number", 
                  "label"=>"Qte",
                  "config"=>array(
                      "yAxisID"=>"second",
            )
                    
                ),
                   
                )
                "options"=>array(
        "yAxes"=>array(
            array(
                "id"=>"first",
                "type"=>"linear",
                "position"=>"left",
            ),
            array(
                "id"=>"second",
                "type"=>"linear",
                "position"=>"right",
            ),
        )
    )
            ));

KoolReport commented on Jul 31, 2018

Oh sorry, it should be:

...
    "options"=>array(
        "scales"=>array(
            "yAxes"=>array(
                array(
                    "id"=>"first",
                    "type"=>"linear",
                    "position"=>"left",
                ),
                array(
                    "id"=>"second",
                    "type"=>"linear",
                    "position"=>"right",
                ),
            )    
        )
    )

My code was lack the "scales" level

arthur commented on Jul 31, 2018

Still not working... Here the code

ColumnChart::create(array(
                "title"=>"Sale Report",
                "dataStore"=>$this->dataStore('PO'),
                "columns"=>array(
                    "salesPerson",
                       "PO"=>array(
                       "type"=>"number", 
                       "label"=>"PO Marge Annuelle",
                       "config"=>array(
                           "yAxisID"=>"first",
                       )
                ),

                 "Qte"=>array(
                  "type"=>"number", 
                  "label"=>"Qte",
                  "config"=>array(
                      "yAxisID"=>"second",
            )
                    
                ),
                   
                )
               "options"=>array(
        "scales"=>array(
            "yAxes"=>array(
                array(
                    "id"=>"first",
                    "type"=>"linear",
                    "position"=>"left",
                ),
                array(
                    "id"=>"second",
                    "type"=>"linear",
                    "position"=>"right",
                ),
            )    
        )
    )
            ));

arthur commented on Jul 31, 2018

The problem seems to come from this.

"config"=>array(
                           "yAxisID"=>"first",
                       )

KoolReport commented on Jul 31, 2018

Please try this:

...
    "options"=>array(
        "scales"=>array(
            "yAxes"=>array(
                array(
                    "id"=>"first",
                    "type"=>"linear",
                    "position"=>"left",
                    "display"=>true,
                ),
                array(
                    "id"=>"second",
                    "type"=>"linear",
                    "position"=>"right",
                    "display"=>true,
                ),
            )    
        )
    )

I add "display"=>true

arthur commented on Jul 31, 2018

No, doesn't work. I think there is a problem with "yAxisID"=>"first" and "yAxisID"=>"second"

KoolReport commented on Jul 31, 2018

You may change "id"=>"y-axis-1" and set "yAxisID"=>"y-axis-1" to see how.

arthur commented on Jul 31, 2018

No, it still doesn't work. Just this doesn't work either

ColumnChart::create(array(
                "title"=>"Sale Report",
                "dataStore"=>$this->dataStore('PO'),
                "columns"=>array(
                    "salesPerson",
                       "PO"=>array(
                       "type"=>"number", 
                       "label"=>"PO Marge Annuelle",
                       "config"=>array(
                           "yAxisID"=>"y-axis-1",
                       )
                ),

                 "Qte"=>array(
                  "type"=>"number", 
                  "label"=>"Qte",
                  "config"=>array(
                      "yAxisID"=>"y-axis-2",
            )
                    
                ),
                   
                )
            
            ));

KoolReport commented on Jul 31, 2018

Is it the page available online. Could you please send us the link, I would like to examine

arthur commented on Jul 31, 2018

You have to login to access. I send you mail with login access

KoolReport commented on Jul 31, 2018

Sure

David Winterburn commented on Jul 31, 2018

Hi Arthur,

Thanks for your email. We've checked your chart page and it seems that you forgot to include options.scales together with columns. Please try the following code to see how it goes:

ColumnChart::create(array(
	"title"=>"Sale Report",
	"dataStore"=>$this->dataStore('PO'),
	"columns"=>array(
		"salesPerson",
		"PO"=>array(
			"type"=>"number", 
			"label"=>"PO Marge Annuelle",
			"config"=>array(
				"yAxisID"=>"y-axis-1",
			)
		),
		 "Qte"=>array(
			"type"=>"number", 
			"label"=>"Qte",
			"config"=>array(
				"yAxisID"=>"y-axis-2",
			)
		),
	),
	"options"=>array(
            "scales"=>array(
            "yAxes"=>array(
                array(
                    "id"=>"y-axis-1",
                    "type"=>"logarithmic",
                    "position"=>"left",
                    "display"=>true,
                ),
                array(
                    "id"=>"y-axis-2",
                    "type"=>"linear",
                    "position"=>"right",
                    "display"=>true,
                ),
            )    
        )
    )
));

For various types of scale for your columns, please check this link: https://www.chartjs.org/docs/latest/axes/cartesian/

Thanks!

arthur commented on Jul 31, 2018

Thanks it works. And is it possible to adjust scale ? Like reduce a little bit the height of number of sells compare to the value ?

David Winterburn commented on Jul 31, 2018

You could try various types of scales (in the above link) for those columns and see which combo of types you think looks best. Thanks!

arthur commented on Jul 31, 2018

So let say I want to change the scale of y-axis-2, which setting would you advise me to use ?

array(
                    "id"=>"y-axis-2",
                    "type"=>"linear",
                    "position"=>"right",
                    "display"=>true,
                )

Thanks !

David Winterburn commented on Jul 31, 2018

Hi Arthur,

If one column's value is much higher than other columns, you could consider using 'logarithmic' scale for it to see if it balances columns' height better. Thanks!

arthur commented on Jul 31, 2018

Last question and then it will be great. If I want to display the sum of all I'm trying this but it doesn't work :

$sum_po = array(
    "salesPerson"=>"ACC",
    "PO, Qte"=>$this->dataStore("PO")->sum("PO, Qte")
);



$data = $this->dataStore("PO")->data(); // <-- get data from store
array_push($data,$sum_po);
$this->dataStore("PO")->data($data); //<--Push back data to store

            ColumnChart::create(array(
...

KoolReport commented on Jul 31, 2018

It should be:

$sum_po = array(
    "salesPerson"=>"ACC",
    "PO"=>$this->dataStore("PO")->sum("PO"),
    "Qte"=>$this->dataStore("PO")->sum("Qte"),
);

arthur commented on Jul 31, 2018

Perfect thanks again, you're the best !

KoolReport commented on Jul 31, 2018

You are welcome :)

KoolReport's Forum

Multiple columns ChartJS #398

Build Your Excellent Data Report