3

I've been trying to aggregate (SUM) by a date field with a custom format (YYYY, MM) but its not working as expected. I tried Views Date Format SQL and Merge rows modules but didn't work for me. Also, some other suggested solution is to group by field (Table settings) which also does not fit my needs.

2014-08-05     12
2014-08-05     15
2014-08-15     20
2014-09-09     10

Im trying to do it as follows:

2014,Aug      47    
2014,Sep      10

Note: My collected dates attributes are year, month , day (i.e. 2014-08-05 00:00:00)

2
  • are you trying to aggregate date by YEAR-MONTH ? Nov 24, 2015 at 15:41
  • Yes, this is it
    – Sam
    Nov 24, 2015 at 17:48

2 Answers 2

0

I recommend trying the Views Aggregator Plus module. It supports many useful aggregation functions. As an exmaple creating the following output:

Industry    Companies             Turnover
Food (2)    Heiny, McRonalds      $566,000,000
Clothing (1)Cenneton              $99,000,000
IT (2)      AcquiB, PreviousBest  $30,000,000
Totals              5             $695,000,000

From this source table:

Industry    Company Name    Turnover
IT          AquiB           $25,000,000
Clothing    Cenneton        $99,000,000
Food        Heiny           $66,000,000
IT          PreviousBest    $5,000,000
Food        McRonalds       $500,000,000

With the grouping taking place on, say Industry, and aggregation functions TALLY, ENUMERATE and SUM applied on Industry, Company Name and Turnover respectively, the final result with a descending sort on the last column will display like below. Note that the module identified three groups. It also added a totals row, Views Calc style.

3
  • But, does it aggregate by date?
    – Sam
    Nov 24, 2015 at 17:49
  • hmmm ... why wouldnt it?! It aggregates by fields if you can include a date field in your View then you can use this module. Nobody will do your homework/task/assignment for you. I'm simply providing information on a module that will likely make your life easier. Although, you may have to do some learning.
    – tenken
    Nov 24, 2015 at 17:52
  • I'm not looking for someone to do it for me tenken, i know pretty well how to aggregate fields and looking only for hints to do it for the date, my issue here is to aggregate date field which I believe has some things need to be taken into account (like format, granularity,.etc) in order to be aggregated properly. I also tried to create a custom field retrieving year, month but didn't work too. Thanks for your answer anyways
    – Sam
    Nov 24, 2015 at 20:05
0

To do that you need to tell the database to chop a part of the field. So if your field column in the database is field_date, then you should have in the views query something like:

GROUP BY FROM_UNIXTIME(field_date, '%Y-%m')

As there's no aggregation plugin (as far as I know), most probably you will need to alter the views query to add that part to the query (there's a hook for that). Also, make sure that field_date is not listed again there, as in that case, the field_date will kick out the other one.

The above example uses a datatime field type (by the database). If you storing the date as a string, you can cut the string end with another SQL function.

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.