How to add up price values when group by month?

Question

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.

Answer 1

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

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

Answer 3

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

Answer 4

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.

Answer 5

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)

Answer 6

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];
    }
  }
}