Row grouping for repeated values

mihalisp

Hi all,

How can i achieve something like this?

How should i build it so that some columns to calculate aggregate values (sums)?

Any help would be appreciated.

colin

Hi @mihalisp ,

DataTables doesn't support colspan - see this thread for more information.

Cheers,

Colin

mihalisp

Ok.

In row Grouping i want to sum a date column (HH:mm) and show it beside of the group name.
i have already summed them with success with the following in drawCallback:

 var api = this.api() , data;
    total_secs = api.cells( null, 17 ,{ page: 'current'} ).render('display').sum();
    total          = secondsToTime(total_secs);

I don't understand how to use it instead of

   var ageAvg = rows
                .data()
                .pluck(17)
                .reduce( function (a, b) {
                    return a + b*1
                    ;
                }, 0);

Can you help me?

mihalisp

Can anyone suggest something here?

Why return a+b?

kthorngren

Why return a+b?

You will want to read up on the Javascript reduce() method.

I'm not really sure what you want to use. I would start by using console.log(a, b); in the reduce() method to see what the values are. That may give you an idea of what is needed for the total. Just a guess but you may need something like return a + secondsToTime(b);

Kevin

mihalisp

I am trying to use Row Group in Datatables to sum time (HH:mm) per user.

But i still get 00:00 for all grouped users.
What am i doing wrong?

 rowGroup: {

     endRender: function ( rows, group ) {       

                const sum_rendered1 = rows
                                     .data()
                                     .pluck('eisodos')
                                     .reduce((acc, time) => acc.add(moment.duration(time)), moment.duration());

                                     console.log([Math.floor(sum_rendered1.asHours()), sum_rendered1.minutes()].join(':'));

                //or 2nd way

                var sum_rendered2  =  rows
                                     .data()
                                     .pluck(17)      
                                     .reduce( function ( a, b ) {

           return moment.duration(a).asMilliseconds() +  moment.duration(b).asMilliseconds()
  }, 0);

 sum_rendered2 = moment.utc(sum_rendered2).format("HH:mm");


         console.log(sum_rendered2);
        return group +' ('+sum_rendered1+')'+' ('+sum_rendered2+')'
        ;
       }, 
     startRender: null,
     dataSrc: 'last_name'
    }

kthorngren

In order to help we will need to see your data. Please put together a simple test case with an example of your data.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

mihalisp

The column with the HH:mm format dates is a computed column.
Is this relevant?

kthorngren

The column with the HH:mm format dates is a computed column.
Is this relevant?

Not sure without trying it. Can you provide a simple test case so we can see exactly what you have? This will make helping you easier for us.

What output did you see when using console.log(a, b); in the reduce method?

Kevin

mihalisp

Where exactly should i put console.log(a, b); ?

The 2 'console' i have in the above code return

1) 0:0

2) 00:00

How can i debug it?How to put a break in each step to see what it returns each time?

Most important,how to see which values from column (in the array returned) are affected??

Thanks.

kthorngren

I would suggest starting off with a simpler example to understand how the reduce method works. Take a look at this one with some strategically placed console.log statements:
http://live.datatables.net/yumiviko/1/edit

It is grouping and summing the age column.

In the reduce() method docs it explains what the a and b parameters are. The first parameter is the accumulator and the second is the current value. So you don't want to apply datetime or other methods to the a accumulator value. It is the subtotal for the loop.

Maybe you can update my example with your data and columns.render function.

Kevin

mihalisp

I try with another column which has only a fixed number,but i get the same errors.

  "render":  function (data, type, row) {
   //return moment.duration(row[14]).asMilliseconds()
    return '2 ' ; }  // or 2

I try a simple sum in endRender:

      endRender: function ( rows, group //, row, data, start, end, display 
             ) {                                 
                console.log('\nGroup:', group);
           var total = rows
                         .data()
                         .pluck(20)
                         .reduce( function (a, b) {
                   console.log('a', a);
                   console.log('b', b);
                        return a + b; 
                }, 0);

          console.log('Group Total:', total);
            return $('<tr/>')
                .append( '<td colspan="3">Sum for '+group+'</td>' )
                .append( '<td>'+total+'</td>' )
                ;

This is the result:
a 0
b undefined
a NaN
b undefined
Group Total: NaN

Why it always return undefined?

kthorngren

return '2 ' ; } // or 2

This is returning a string. Removing the quotes should return an integer.

In the exemple you are starting from it has return a + b*1;. I believe the *1 is a technique for Javascript to convert the string based number to a real number for the equation.

Why it always return undefined?

Without actually seeing it its hard to say. Please build a simple test case or update mine so we can more effectively help you.

Kevin

mihalisp

The column i want to sum is a computed one.

For this column data:null,so how should i use .pluck?

.pluck(what?)

.pluck(20) doesn't work,even if i set this column to return 2 or "2".

the 20th column is a computed one not a column in the JSON output from db.

I also believe that *1 is necessary after return a + b.

For some reason in my case b is always undefined.

What's the difference with api.column() instead of rows.data()?

Thanks

mihalisp

Sorry,i just found this :
https://datatables.net/forums/discussion/46789/problem-with-adding-column-values-and-rowgrouping

where Allan suggests that for a computed column rows.cells( rows.indexes(), 7 ).render() should be used.

What exactly should this replace?

    rows
   .data()
   .pluck( 20 )  

kthorngren

Here is an updated example of what Allan suggested:
http://live.datatables.net/cixekuqi/1/edit

Is there a reason you won't build a test case for us to help you? Shouldn't be that difficult. Just put a sample of your data in the table.

Kevin

mihalisp

It worked!!!

I am really sorry,next time i will build a test case.
It is not difficult after all.

I think that Group Row Sum for cells in a rendered column is a common issue for many people.

I also use the Rows Group plugin from here https://datatables.net/forums/discussion/29319

When i use them both at the same time,the Sum row for Group is shown two times.
Have you got any idea why this is happening?

Thank you so much Kevin!

colin

We're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Cheers,

Colin

mihalisp

ok i will make a test case,but how i will add the js of the custom RowsGroup plugin in the test case?

allan

You can include arbitrary libraries on http://live.datatables.net , JSFiddle, CodePen, etc.

Allan

mihalisp

Here is the link
http://live.datatables.net/cixekuqi/3/edit?html,js,output

Thank you guys,your help is appreciated!

colin

Hi @mihalisp ,

That demo helped, thanks. The issue is because you've got that third-party RowGroup included as well - both are displaying that footer information. You'll need to decide which one to use, because as you're seeing, they're going to stumble over each other.

Cheers,

Colin

mihalisp

Both calculate correctly the sum.

Which line of code should i omit from one or the other,so that the sum row doesn't show twice?

colin

It's your choice - I imagine both extensions have pros/cons, so whichever works for you.

This is the third-party extension:

rowsGroup:[1,0],

and the block starting with this is ours:

rowGroup: {

Cheers,

Colin

mihalisp

I know which one is rowGroup and which is rowsGroup.
I thought that rowsGroup is an extension of rowGroup and can't work as standalone.
I looke into both plugins' code but i couldnt find out what to comment out from rowsGroup so that it doesn't show two times the sum row.

colin

I've no idea about rowsGroup, it's not one of ours. But rowGroup isn't dependent on rowsGroup.