2

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: enter image description here

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:

enter image description here

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.

  • OK, making progress ... extra questions (sorry ...): (1) do you agree I re-insert that image from revision 1 (about "chart add-on splits apart", near the end)? (2) What exactly is that "title", I bet it is that company name (but YOU need to confirm that)? (3) Which charting engine are you using (Google charts or Highcharts)? (4) If you say "combine all the fields for (eg) 2017": How to combine them, how do you want the combination to look like? Wild guesses: show the SUM (5+2 = 7, so only the 7) or a something where you still see the "5" (for company A) and the "2" (company B) for 2017? – Pierre.Vriens Sep 6 '16 at 15:54
  • Pierre, 1) The image looks great and thanks for the table. Sure, if that helps. 2) The title is the company name 3) I think this is the Google charts. In the view selection to display the data, I selected Charts -> then selected Column in the settings of the chart. 4) I want to sum all values from a field (e.g. 2017) into one column, so 5+2=7 – Mike Sep 6 '16 at 16:10
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: enter image description 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.

  • Looks like the "PS" in my original answer (to use a stacked column chart) was something you also wanted to consider (though that's not what your question was about). So I've improved my answer to massage that PS in what now is with "Workaround". Feel free to use from it whatever fits (eg: use same colors?) to further improve your custom module .... About your last phrase: are you familiar with this? – Pierre.Vriens Sep 7 '16 at 12:16
  • Pierre, sorry for the whole day delay. I looked at the "PS". I haven't tried that method yet, but it may be something that I'll use in the future (which might be pretty soon). Having multiple color coded bars isn't an issue since it helps visualize how much each company's values are in each column. One more thing, I've been able to get individual values from each company, so the custom module is a success. Thanks for the help throughout this process. I'll be updating my answer. – Mike Sep 8 '16 at 17:32
0

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.

  • I'd prefer not to use the API, but if I do, I'll check out this one. Thanks. – Mike Sep 6 '16 at 17:04
0

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).

  • Pierre, I think I'm going to follow the step by step to create multiple charts. I'll update this comment once I have done so. Also, I don't have a field for sum for adding up all the values of the columns in a row, BUT I can use the drupal module tables with aggregation options to do that. However, I don't know how I can access the data in that field. – Mike Sep 6 '16 at 16:54
  • @Mike merci for the feedback. My extra thoughts on it: (a) the core of your question can be reduced to "how to create a tabular view which shows the sum for each year?" (that's a views question) and (b) I don't think you need to create multiple charts (instead you only need 1 chart, to visualize the tabular data that contain the sum for each year). – Pierre.Vriens Sep 6 '16 at 17:12
  • Pierre, (1) I think I have that with the table with aggregations options. Here's the imgur link: imgur.com/a/1SEXT This shows the sum of each year. I would like to use that sum in the charts, but don't exactly know how. (2) I would like to stick to one chart since it's easier, but don't know if it's possible for now. – Mike Sep 6 '16 at 17:28
  • Pierre, I tried using your experiment a while back and while this approach would work, it would result in too many nodes. The Views Merge Row module solves this issue, but separates for some reason. I think in the end, I'll have to make a custom module. If anyone else posts an answer here, I'll definitely give it a try. – Mike Sep 6 '16 at 19:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.