How to combine values of same field in a single column of a chart created with Charts?

Question

My question is about merging values for both the title field (= the company name) and various fields. I'm currently using the Charts module.

In the picture below, it shows a column chart that displays a content type with a field of title, integer field (labeled 2017), and another integer field (labeled 2018). In this view, I have a chart (showing title and 2017) and a chart add-on (title and 2018).

The graph looks like this:

Here is how the table about these data looks like:

  Company   ! 2017 ! 2018
  ----------+------+-----
  Company A ! 5    ! 6
  Company B ! 2    ! 3

What I want to do is SUM all the values of the 2017 field into one column and values of 2018 into one column.

I tried using the Views Merge Rows module and configured the option to SUM all the values of 2017 and 2018 into one column, but it's not working for me. The chart (showing title and 2017) works since it combines all values, but the chart add-on splits apart and I don't know why.

This is what I get:

Is there a way to combine all values of 2017 and 2018 into one column for each year? "Combine" means that I want to sum all values from a field into one column, so for (e.g.) 2017, it should be 7 (=5+2).

Note: I'd prefer to not Use the Charts API, but I will if I have to.

Answer 1

Edit: The module now shows individual values for each company and takes the company's latest published version's vales. So, if I made another entry for Company A and it's published, the chart will only show the latest version's values. I've also edited the body and included a picture of the final result.

It looks like building a custom module answered my own question. I had to code a module with the charts API and create a custom block to show the data (it was a huge pain writing the functions and retrieving the data).

Instead of adding all the values into one column, what I've done is take a company's values for each year, place it on the chart, and other company values stacked on the previous company values as shown here:

To anyone else now or in the future with the same problem as me, here's a code snippet that I used:

$chart = array( '#type' => 'chart', '#chart_type' => 'column', '#stacking' => TRUE, );

Include the code ^above in your function that displays the block/module.

You will need to make your own functions and may be you'll need to implement some of the functions as shown in WD Tutorials for Drupal.

Also, for getting the field data, I made a function that shows all the years (in sequential order). I made several variables: holding a result of an array to store the years, counters (for an array counter, year counter, etc.), an offset for the current year, and a variable for a hard limit (having an arbitrary hard limit is bad). I made use of two while loops that checked to see if the data field (e.g. field_data_field_(insert name of field)) to check if it exists and to see that year contained any rows (indicating that having any values in the table shows that it exists).

As for the other functions, I made separate functions such as filtering duplicates, retrieving all node IDs that are Company content type, and getting each of the node's ID values.

For anyone that's stuck on Drupal's PHP syntax, I would suggest looking at PHP first and then comparing it to Drupal's PHP documentation (confused me at first).

Thanks to all that offered me help during the process. It got me thinking better.

Answer 2

Years ago I was asked to make a usage statistics report for the workflow tool that we had cast in drupal. This report showed tables of workflow requests by type by client by month, with sub totals for each and exposed filters and a csv export. Once it finally worked it was a thing of beauty, but it sure was a pain to build.

The way I accomplished this was with some extra processing in a hook_views_pre_render and then some empty Markup fields with specific names. The view queries the data in the most exposed way possible, lots of fields for my use. Then the pre_render loops through all of that data with an extra array doing the sums and tallies. Then I inject those values in the appropriate markup fields for each row. The column subtotals meant I added an extra row to the bottom of the table in the pre_render. Because all of this number manipulation happens in pre_render, the fields are then handed off to the chart display processor to make your bar graph without fuss.

I would include code examples, but this was back in drupal 6.

Answer 3

To "sum all values from a field (e.g. 2017) into one column, so 5+2=7" (as in your comment), you need to tune your view (tabular display) first, similar to what is described also in Steps to create charts using the views UI. Looking at your current (tabular) display of your data, it is not yet showing the SUM of the data for which you want to create a chart.

Typically this is done using the Views aggregation of Charts, whereas you use SUM (instead of COUNT which is used in, eg, the delivered Views examples that come with the Charts module, as shown also in the demo site). For that you don't need Views Merge Rows to get it to work.

If you would only have 1 field that contains a year as value, and another field with some integer value, then you could use aggregation to group each year together, and use "sum" as aggregation function for the other field with the integer value. You however have a 1st integer field (labeled 2017) and a 2nd integer field (labeled 2018). That's different of course. A possible solution could be to think of a way to combine your 2 fields in a single.

Workaround

Another solution to visualize those data with a single column (for each year), is to create a stacked column chart of it, similar to this demo example. Notice that by such stacking, automagically the height of each column corresponds to the "sum" of all its values of course.

However, that's something you can only do (for now) using the Charts API. For more details on that, refer to the issue about Stacking column/bar charts. With comment nr 3 of it, there is a code snippet that will help to get you going. This is how it looks like:

function MYMODULE_chart_alter(&$chart, $chart_id) {
  dsm($chart_id); // Need Developer module enabled for dsm to work
  if ($chart_id == 'MYCHARTVIEW__DISPLAY') { // Watch out for those double underscores
    $chart['#stacking'] = TRUE;
  }
}

Note the test about $chart_id in it, to only enable stacking for those charts that you really want to be stacked.

And if you would use the same color for all companies (which you can also specify via the Charts API), you would end up with 1 column for each year, showing the total only (since you cannot make a distinction between the various companies if you'd use the same color).