Create additional column for calculations (sum, difference, average)

I've started implementing this so that for each field in the table you can select if aggregation for their column and/or for their row should be calculated. For now, this only work for the sum, others like average etc can be added later easily.

Please give the attached patch a try. It should apply to the latest dev release and you can then go into the view sstyle setting where you turn on aggregation for the amount field.

Please let me know if that's working and what the issues may be.

Thanks for your quick feedback, that's good news.

Regarding labels and formatting I was thinking this: the label should be available in the views plugin settings next to the select field where you enable aggregation. Formatting however, I'd like to use the field formatting settings that are applied to the field which is aggregated.

What do you think?

Right, this is now also included and you'll find the results in the latest dev release of this module. Please give it a try as soon as it's available for download from d.o

hi - this looks exciting possibility.

just tried dev to get row and column sub-totals

is the below an error i am getting because some of my 'cells' are null as there is no data for some options?

or, more likely, have i simply mis understood the required set up.

The Pivot Table looks great and gives me the results I wanted in the Rows and Columns - so thank you for putting this module together - but either I get no 'SUM' results or the below error depending on which options i try.

I am assuming that to have a sub total for the rows, where the data being aggregated is 'field 3' that I should be setting the Row Aggregate to 'sum of values' for Field 3?

An AJAX HTTP error occurred.
HTTP Result Code: 200
Debugging information follows.
Path: /admin/structure/views/view/members1/preview/default/ajax
StatusText: OK
ResponseText: 
Fatal error:  Cannot access empty property in /srv/www/internetnz/current/sites/all/modules/views_pivot/views_pivot.theme.inc on line 329

Your setup looks correct. Debugging the error however is pretty hard, looking at just the code hasn't helped identifying the reson for the problem. Is there a way you could send me the data (db tables and exported view definition) or could you debug the lines 327-329 in views_pivot.theme.inc and probably find out more details what's going on there?

thanks for your prompt reply - the data is complicated as it is civicrm + drupal data

i will fire up a view using just drupal data that has some NULL values and see if that triggers same issue

and get a civi view that has no null values in case it is at that end ;-)

and then get someone to look over my shoulder and see if we can debug it a bit

grabbed from git in case the above 'commit' was not in latest dev

now seeing

An AJAX HTTP error occurred.
HTTP Result Code: 200
Debugging information follows.
Path: /admin/structure/views/view/user_pivot/preview/default/ajax
StatusText: OK
ResponseText:
Fatal error: Call to undefined method views_handler_field_user::set_items() in /srv/www/chch.alted.org.nz/sites/all/modules/views_pivot/views_pivot.theme.inc on line 330

Not sure what I am doing wrong, or what is different about my system.

it may be i just don't get what the setting should be ie if field1 is my row, and field2 is my column, and field3 is my 'data', and if i want to see eg Sum of Values at the end of each Row, do i set the Row Aggregate for Field 1 or 3?

I tried both, Field 1 gives nothing, Field 3 throws the above

here is about as basic a view as we can do ;-)

3 fields
user name
User email
user uid

with name as row, email as column, uid as data in pivot table cell

with the assumption that I should then be able to use the Row Aggregate = Number of Values (not a useful set of data but perhaps it lets you very easily either confirm the error - or explain the user error

FYI Drupal 7, Views 3 etc everything up to date

Thanks a lot @petednz, this is great help to hopefully nail this down quickly. I have improved the code and comitted that to the dev release. Please give that a try as soon as it is available for download and let us know if it now works.

again thanks for the prompt engagement

updated via git pull

With the new code ....

setting to 'number of values' for Field3 for both Column and Row Aggregate does now show values (as per updated View attached).

changing them to 'sum of values' gives the error below and shows no results

n AJAX HTTP error occurred.
HTTP Result Code: 200
Debugging information follows.
Path: /admin/structure/views/view/user_pivot/preview/default/ajax
StatusText: OK
ResponseText: 
Fatal error:  Cannot access empty property in /srv/www/internetnz/current/sites/all/modules/views_pivot/views_pivot.theme.inc on line 348

(nb i was getting no result but no error when I had the uid field set to 'link to user')

I am reattaching the view with it set so it shows results of 'number of values' - can you confirm that changing either to 'sum' or 'average' creates the error?

Thanks for your further request. I've had another look and realized that you're aggregating over a property (uid) rather than a field. I have improved the code so that it can deal with that as well.

However, there must be another difference. I guess that comes from the PHP version. Which PHP version are you using?

In any case, you can test the latest dev release again and see if that solved your problems now.

php is 5.4

and yes that fix fixed things for UID - many thanks.

still no joy with our civicrm data but we will take a poke in the views_civicrm code as we expect it is an issue of how the data is getting in to the view that means it is not 'summable'

thanks for your work on this

Hi! Same problem and situation to MrPeanut's here. I have to count nodes (subscribers) grouping them by two different entity reference fields (location and belonging group) and get the total row and column sum for each, but the total values don't show up. Where I go wrong? Thanks.

This was an interesting one: you are using an aggregated field (count on gadgets) but views isn't counting properly. You can see that when you change your view into a normal table (instead of a pivot table). What you're getting is a list of 15 records with a count = 1 for each row. That means, views is just listing all the rows but doesn't count those together where gadget and store are having the same values.

So, the view as such is not producing the correct results, even without using the pivot formatter.

You can also see the wrong result, that you only get values of 1 in each of the cells where as you would expect higher numbers in some cases, even without calculating the totals per column or per row.

However, while I was on it to analyze that, I found a way to resolve that problem which isn't caused by views_pivot (as explained above). And while solving that alien problem the aggregation for pivot got solved automatically as well. For now, this is only implemented and tested for views count aggregation.

PLease give the latest dev release a try and let me know if you're satisfied with this.

@jurgenhaas - Thank you very much! I had an aggregated count column like MrPeanut's above. I've just installed your latest release and by disabling column aggregation everything works like a charm!
Thanks for your great work! :-D
Cheers!

@Cheope, great to hear, thanks for your feedback.

@MrPeanut, not a bad idea but I wonder about a little detail: if you use both, column and row aggregation, a grand total is easier because we already have a cell in the table where to put the result. However, if one sets up a view with either only row aggregation or only column aggregation, then we're missing either a row or a column where we cut stick the grand toal into. Any ideas on how we should handle that?

Here I want to calculate some columns.
I collect readings from a machine monthly basis.
I want the result between two months. (two months can be filter) in pivot two columns
meter = last month - previous month reading
meter with tax = meter * 12%
Meter with another Tax = meter with tax * 5%
Total = meter with tax + meter with tax + Meter with another Tax
Can we do this

@nadeeke that sounds like a very specific requirement and I wonder if more than one site is going to need something like this. Unless we manage to define a generic requirement that is useful to a wider range of use cases, I would want to include that into a module. You should either develop a custom module for this or use the views_php module to define the output with a little PHP snippet that's assigned to the view.

@MrPeanut regarding your summary of sums I was wondering if we should enable that only in cases where both column sum and row sum was enabled? Because in that case we already have that one empty cell where we could output that overall result.

@MrPeanut OK, I've committed an attempt to the latest dev, please let me know if that works for you as expected.

Thanks @MrPeanut so it's time for a new release :-) Going to publish that the next couple of days.

Thank you very much for your reply; I want to calculate again with calculated column. but in views we have no column named that. FInal problem is total of total grand total please help to generate it. Thank you again this is a great help.

@nadeeke it sounds that what you're asking for has been implemented yesterday. If you turn on SUM for columns and rows, then this module is now also outputting the SUM of the sums in the bottom right corner. If this is not what you're looking for, please open a new issue and describe in detail what it is you're looking for - ideally by providing some examples, that's easier to understand.

Thank you @jurgenhaas
Can you give us a way to identify this aggregated column to reuse in the global php or global text field as available variable or a replacement pattern.

Don't think that's possible. The value(s) get calculated at the very end of the themeing layer and I don't believe there is a way to pass that back as a token. I might be wrong and would of course accept patches. But again, that is a different issue and this one is marked as closed and fixed.

Thank you very much

Thanks for this great module. I have similar problem like MrPeanut about aggregated columns in pivot table. I want to list all types of items related to a topic and then aggregated them. The topic node, item types nodes (MCQs nodes, CRQs nodes, ERQs nodes) are all group content.

If I choose a table layout, the data seems to be displayed correctly (see image) which means the views is aggregating it correctly, however switching to pivot with topic title in 'rows', Item Type in 'columns' and COUNT(Distinct) NID as 'data', I cannot aggregate a "Sum of values" in either row or column. When selecting "number of values", it displays the count correctly but I want the sum of count of NIDs.

Now in #23, I don't understand the "alien" problem mentioned by jurgenhaas.

"And while solving that alien problem the aggregation for pivot got solved automatically as well"

There must be something wrong with my config of Pivot Table. Please suggest me a solution. I tried with the latest dev version with same results.

As this is a closed issue it would be better to start a new one and refer back to this one if necessary or helpful. And also, you may have hidden attachments from other users by accident, I've reverted that change.

Looking at your screenshot it looks like you're combining two columns (ID and Title) into one and that one is then the row for the pivot table. That is probably the reason for this. To verify that assumption, please remove the ID or Title field and work with a simple one only - or define the title as a "Pivot Extra Row" and leave it in its own column.

@juegenhaas: Yup they were added accidently. I have opened a new issue here as a support request.

Thanks