How do I Group Duplicate Labels in Column Chart

Giulia Fois commented on May 2, 2019

Hello, I'm creating a column chart that for each hour in the scope of several days shows a value. However, I don't want my x-axis labels to have the granularity of the data I'm showing; I just want to display each day once every 24 columns. To be clearer, the situation I have right now is the following: The situation I want to achieve is (I haven't been precise with the dates, I post it just to give you an idea of what I want to achieve):

Is there a way to achieve it? Thank you so much.

David Winterburn commented on May 7, 2019

Hi Giulia,

Did you use our Google Chart widgets? If so, to group the date labels to months please check this documentation of Google Chart's data group: https://developers.google.com/chart/interactive/docs/reference#google_visualization_data_group

You could pass any additional option to the "options" property when creating a chart widget.

We will see if we could create an example later. Thanks!

Giulia Fois commented on May 7, 2019

Hello, Thank you for your reply. Yes, I used KoolReport's Google Chart widget. Thank you for linking me that part of Google Charts' documentation. I'm trying to integrate it in my code, but it doesn't seem to work (primarily, I guess, because of the fact that I'm trying to use a javascript function in my php code and I'm not sure I'm doing it right). This is, by far, my attempt:

<?php

use \koolreport\widgets\google\LineChart;
?>

<script type="text/javascript">

var dataTable = <?php echo $this->dataStore("csv_data_processed")->toJson(); ?>;
var keys = {column: 0, modifier: function(date) { return date.split("-")[0]; }, type: 'number'};
google.visualization.data.group(dataTable, keys);
</script>
<?php
LineChart::create(array(
    "dataStore" => $this->dataStore("csv_data_processed"),
    "columns" => array(
        "ts_from",
        "avg" => array(
            "type" => "number"
        ),
        "min" => array(
            "type" => "number"
        ),
        "max" => array(
            "type" => "number"
        )
    ),
    "options" => array(
        'hAxis' => [
            //'showTextEvery' => 24,
            'gridlines' => [
                'count' => 12    
            ]
            //'textPosition' => 'out',
        ],
        'vAxis' => [
            'showTextEvery' => 5,
            'viewWindow' => [
              'min' => 30  
            ],
            'gridlines' => [
                'count' => 12
            ]
        ]
    )
));

I managed to format the date into "d-M", and I want to group my dates by day, thus my action of splitting the date and taking the first part. My modifier function takes a date in my format as its input (for example "19-April") and returns the day (in my example it'd return 19). However, this solution doesn't work. I'm quite sure it is more of a syntax problem. Where is the mistake in my code? Thank you so much.

David Winterburn commented on May 7, 2019

Hi Giulia,

Please remove the group javascript code. Maybe I misunderstood your question. If you want to only show a label for every 24 columns and format the displayed label, please try this:

LineChart::create(array(
    "dataStore" => $this->dataStore("csv_data_processed"),
    "columns" => array(
        "ts_from" => array(
            "formatValue" => function($value) {
                return date("m-Y", strtotime($value));
            }
        )
        ...
    ),
    "options" => array(
        'hAxis' => [
            'showTextEvery' => 24,
            ...
        ],
        ...
    )
));

Let us know if there's any problem with this solution. Thanks!

Giulia Fois commented on May 7, 2019

Thank you, the solution works. However, I'm not able to display any vertical gridlines. I would like to have one for each date shown in the chart.

Giulia Fois commented on May 7, 2019

Update: I managed to achieve the result I wanted. I post my view code for future reference, in case anyone needs help on a similar issue.

<?php
use \koolreport\widgets\google\LineChart;
?>
<html>
<?php

LineChart::create(array(
    "dataStore" => $this->dataStore("csv_data_processed"),
    "columns" => array(
        "ts_from",
        "avg" => array(
            "type" => "number"
        ),
        "min" => array(
            "type" => "number"
        ),
        "max" => array(
            "type" => "number"
        )
    ),
    "colorScheme" => array("#3B8778","#6FF2C2","#D5EFD2"),
    "options" => array(
        'legend' => 'none',
        'hAxis' => [
            'ticks' => $this->ticks
        ],
        'vAxis' => [
            'showTextEvery' => 5,
            'viewWindow' => [
              'min' => 30  
            ],
            'gridlines' => [
                'color' => '#d1d1d1',
                'count' => 12
            ]
        ]
    )
));
?>
</html>

The ticks variable is an associative array and contains all the dates I want to show indexed by a number. To be clearer:

$ticks = array( ["v" => 1, "f" => "19-Apr"], ["v" => 2, "f" => "20-Apr"], ... , ["v" => 7, "f" => "25-Apr"] );

This format is strictly required by Google Charts in order to obtain gridlines right on non numeric/date h-axis values (keep in mind that Google Charts require specific javascript date objects, so PHP-generated dates are interpreted as strings). The "v" key contains a Google Charts indexable value, while the "f" key contains the text you want to show on the chart's h-axis label.

This is my result:

Hope this helps! Thank you again David for your support.

David Winterburn commented on May 8, 2019

Thanks, Giulia, for your sharing! It would certainly help a lot of developers with similar problems in the future. Great work!

KoolReport's Forum

How do I Group Duplicate Labels in Column Chart #846

Build Your Excellent Data Report