6

I have a Views table view that shows bank transactions. Each transaction has:

  • A price field (which consists of a single positive OR negative amount)
  • A date field

I would like to display the transactions grouped by month and then display a total of all the price fields for each month.

How can I do this?


Steps I have tried

1) Use Views built in tools

In the setting for a table view, you can chose to group entries by a field. So I group my fields by month.

I then go to other > Use Aggregation and set it to “on”.

Finally I select “Sum” in the aggregation options of the price field.

Results:

  • Entries are grouped by month Months are not in order (It goes March, January, February, December)
  • The total of the prices fields for each month is not displayed.

2) Use Views Calculation module

This requires a patch to support Group fields. Unfortunately, the patch does not work with price fields (see comment #118). My own testing confirms this.

3) Use Views Aggregator Plus

Views Aggregator Plus module is designed to be an alternative to Views Calc.

In Views format, I select a “Table with aggregation options” I apply the “column function” to my price field and select “sum” I apply the “group function” to my date field and select “group and compress”.

Results:

  • Only the first transaction for each month is show (The other transactions are hidden)
  • The total is displayed, but it is incorrect and doesn’t seem to relate to anything.
  • About "2)": can you be more specific about "does not work" (e.g. via an edit of your comment #119 in that issue. Also, what is "special" about such price field, how does it differ from "classic number" (as in comment # 118 of that issue)? – Pierre.Vriens Apr 21 '16 at 8:52
  • @Pierre.Vriens The price field has a field type of float. When used with View Calc, the total price is displayed as 0.00. – big_smile Apr 21 '16 at 17:09
  • Shouldn't price field be of type Decimal rather? Decimal is mire precise than float. – J. Reynolds Apr 22 '16 at 7:32
  • @J. Reynolds I've been using float for over a year. Is it possible to change it without losing my data in the fields? – big_smile Apr 22 '16 at 20:36
  • Apologies for not being able to offer a bounty. I spent a lot of time implementing all the suggestions, but couldn't get any of them to work sadly. I'm not sure why, but I will investigate. If I ever find a working solution, I will come back and mark the correct answer. (Or provide an answer if I find a new approach). – big_smile Apr 28 '16 at 17:49
3

You can use views date format sql with simple in built views aggregation. No need to use views aggregator plus.

  • That doesn't seem to work with date fields: drupal.org/node/1830436 – big_smile Apr 19 '16 at 15:36
  • I disagree. It does work. Install the latest patch, then make sure to clear your cache. After that the 'Use SQL to format date' tick box becomes available. The only thing that does not work is getting the sort right. For that you can do a custom sort in hook_views_pre_render or something similar. – J. Reynolds Apr 22 '16 at 6:50
  • @J. Reynolds - Yes it works with the patch. Thanks! – big_smile Apr 23 '16 at 16:02
  • @vgoradiya: I use "format > table" I then set the date as the group. I then turn on Views built in simple aggregation. I then set my price field to "sum" aggregation. However, all the fields in the View display disappear. What am I doing wrong? – big_smile Apr 23 '16 at 16:04
2

When using "Views Aggregation Plus" as described in your question, you also need to change the date field to use a Custom PHP "Date Format" something like "F Y".

Then disable the "group function" for date column in the “Table with aggregation options”.

Also make sure views has "Use aggregation",

This should give you totaled rows for each month.

Also make sure views_date_format_sql module is enabled.

  • @Z Z when you say "Custom PHP Date format" are you referring to the option marked "Choose how users view dates and times:" (This option is found for the date field in Views)? When I follow your instructions, the results are grouped by month, but the price fields are displayed as "1" instead of the original price value. Also, the dates are out of order (It goes March, Jan, Feb, Dec). Thanks for any help you can offer. – big_smile Apr 21 '16 at 14:21
  • Also, when I use "views_date_format_sql" module, all my date fields display "Broken/missing handler". – big_smile Apr 21 '16 at 14:34
  • To clarify, When you edit the view, and go to the date field, edit the date field. You will find the "Date format" field - choose "Custom", then it will provide a field "Custom date format" in which you enter the PHP format. Further down, choose " Use SQL to format date". Then save the field. You can fix the sorting by adding Date to "Sort Criteria" section of the view, and choose type of sorting you want – Z Z Apr 21 '16 at 14:51
  • Is this with "views_date_format_sql" enabled? With this module, all my date fields display "Broken/missing handler". Is there a way to get the date fields to display when the modules are enabled. I've searched around but can find nothing! – big_smile Apr 21 '16 at 14:57
  • Yes, views_date_format_sql" is enabled. Make sure the Views "Other" Section you select "Use aggregation" to be Yes. Then for the Price field, edit it again and use SUM as its "aggregation type" – Z Z Apr 21 '16 at 14:59
1
+75

I realise your question is not about creating a chart from these data. However there is an interesting Charts-issue you may want to look at (for which the challenge is similar to your question here). I.e. Graph node input over time (support date formatting on a continuous axis). In Comment #3 of that issue is stated: 'The trouble here is that neither Views nor Date modules provide aggregation options for date-based fields.'.

Part of the recommended solution there is to (also) use the Views Date Format SQL module. Here are some details (from its project page):

The Views Date Format SQL module allows to format date fields using SQL. This enables group aggregation for date fields using the choosen granularity.

The core functionality is to remove the date formatting from render() and put it in query(). I.e. format date values using SQL's DATE_FORMAT rather than PHP's format_date. This is achieved by assigning a new default handler to node 'created' and 'changed' date fields. This handler extends and overrides views's build in views_handler_field_date.

The UI is completely unobtrusive, only a single checkbox "Use SQL to format dates" is added to the handler configuration popup.

Some more suggestions from that very same comment:

If you're doing grouping by node post date or other built-in fields however, this problem can be solved by using the Views Date Format SQL module. It adds a nice little checkbox that allows you to group dates by a custom formatted string. In this screenshot, I set the custom date to be "M Y" (which translates to Oct 2012 for example), then checked the box for "Use SQL to format Date".

enter image description here

0

I made a site once that provided a service. Clients would log in and request the service, then the company would process them through a workflow and generate a report. The company wanted to know which clients were requesting how many of what type of report on a monthly and weekly basis. I wrote a statistics report set of views and bundled it into a feature for them. In order to get the sub totals by type and also the grand totals by client in each table, I had to use a combination of placeholders in a footer area and hook_views_pre_render to do the summations and replace the placeholders.

This hook has the ability to rewrite the rows[] section of the view before render, so you can even change what the view is going to show as the data, but there are better ways to do that. This approach gives you access to all the data that the view has fetched and lets you change and/or add things before the theme gets to work on making it look pretty. I would recommend making the view data as simple as possible to give your custom processing more room to work. Empty global text fields in the table and in the header/footer give you somewhere to put the new information into the view when you are done processing it.

Having a look at the structure of the view object before you are manipulating it will make it easier to grab the data for your sums and also put it in place. I use this recursive print tool in a watchdog statement because not every hook can print_r to the screen. I think the hook_views_pre_render can though, so you probably don't need the watchdog log.

0

Have you tried with

Force using fields

checked ? (Under Format -> Settings)

Thsi is in german, but you'll find it under Format Format: Unformatierte Liste | Einstellungen (underlined)

enter image description here

  • Yup, that should 'unlock' the grouping options for the format settings. – Neograph734 Apr 21 '16 at 13:08
  • @rémy @neograph734 I can't find Force Using Fields anywhere! Which method does this apply to (1) Views built in tools, (2) Views calc module or (3) Views Aggregation plus? Thanks! – big_smile Apr 21 '16 at 14:09
  • that's a standard view setting. found under Format: Format, i guess you have Table (with aggregation), left, there's a settings link where you find that property – rémy Apr 21 '16 at 15:13
  • Is this for Drupal 7? I've checked under "Format > Table" and "Format> Table with aggregation options" but I can't find Force Using Fields anywhere! Thanks in advance and thanks for your patience. – big_smile Apr 21 '16 at 15:47
  • yes - it's D7 .. if i choose format > table, it isn't there .. try to change to unformatted list, tick it, save and put the format back. tables uses only fields, and probably has this setting as default .. (or maybe not ...) – rémy Apr 21 '16 at 15:57
0

Yo can do It using pre render hook in your theme template.php or in a custom module.

function your_theme_views_pre_render(&$view) {
  if ('your_view_name' == $view->name && 'your_display_name' == $view->current_display ) {
    foreach ($view->result as $key => $row){
      /* here you can acces each row data (view result) ,
      take all the values for the rows on each month
      (which you get from the date field) and sum them,
      you have the values you need */
      $date = $row->field_field_date[0]['raw']['value'];
      $month = /* get the month from the date */
      $month_price = $row->field_field_price[0]['raw']['value'];
      $_SESSION['month_sum'][$month] += $month_price     
    }
  }
}

Once you have the values you can keep them and modify the output with a preprocess hook. Another (and easier way) if you won't use the price field for anything else, you can iterate again through the $view->result and change every price with the sum of all the prices on his month so, on the final output you will have it already, resulting in something like:

function your_theme_views_pre_render(&$view) {
  if ('your_view_name' == $view->name && 'your_display_name' == $view->current_display ) {
    foreach ($view->result as $key => $row){
      /* here you can acces each row data (view result) ,
      take all the values for the rows on each month
      (which you get from the date field) and sum them,
      you have the values you need */
      $date = $row->field_field_date[0]['raw']['value'];
      $month = /* get the month from the date */
      $month_price = $row->field_field_price[0]['raw']['value'];
      $_SESSION['month_sum'][$month] += $month_price;     
    }
    foreach ($view->result as $key => $row){
      $date = $row->field_field_date[0]['raw']['value'];
      $month = /* get the month from the date */
      $row->field_field_price[0]['raw']['value'] = $_SESSION['month_sum'][$month];
    }
  }
} 

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.