Row grouping for repeated values

Row grouping for repeated values

mihalispmihalisp Posts: 127Questions: 22Answers: 0

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.

Answers

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    Hi @mihalisp ,

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

    Cheers,

    Colin

  • mihalispmihalisp Posts: 127Questions: 22Answers: 0

    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?

  • mihalispmihalisp Posts: 127Questions: 22Answers: 0

    Can anyone suggest something here?

    Why return a+b?

  • kthorngrenkthorngren Posts: 20,142Questions: 26Answers: 4,736
    edited May 2019

    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

  • mihalispmihalisp Posts: 127Questions: 22Answers: 0

    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'
        }
    
  • kthorngrenkthorngren Posts: 20,142Questions: 26Answers: 4,736

    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

  • mihalispmihalisp Posts: 127Questions: 22Answers: 0

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

  • kthorngrenkthorngren Posts: 20,142Questions: 26Answers: 4,736
    edited May 2019

    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

  • mihalispmihalisp Posts: 127Questions: 22Answers: 0
    edited May 2019

    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.

  • kthorngrenkthorngren Posts: 20,142Questions: 26Answers: 4,736

    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

  • mihalispmihalisp Posts: 127Questions: 22Answers: 0

    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?

  • kthorngrenkthorngren Posts: 20,142Questions: 26Answers: 4,736
    edited May 2019

    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

  • mihalispmihalisp Posts: 127Questions: 22Answers: 0
    edited May 2019

    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

  • mihalispmihalisp Posts: 127Questions: 22Answers: 0

    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 )  
    
  • kthorngrenkthorngren Posts: 20,142Questions: 26Answers: 4,736

    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

  • mihalispmihalisp Posts: 127Questions: 22Answers: 0

    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!

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    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

  • mihalispmihalisp Posts: 127Questions: 22Answers: 0

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

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

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

    Allan

  • mihalispmihalisp Posts: 127Questions: 22Answers: 0

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

    Thank you guys,your help is appreciated!

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    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

  • mihalispmihalisp Posts: 127Questions: 22Answers: 0

    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?

  • colincolin Posts: 15,112Questions: 1Answers: 2,583
    edited May 2019

    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

  • mihalispmihalisp Posts: 127Questions: 22Answers: 0

    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.

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

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

This discussion has been closed.