Custom Aggregations

[mercury85]

Hi everyone,

I was wondering if there was a little more guidance on custom aggregations.

For example lets say we had a 3 tier group, but the lowest group had a couple of children.  I dont want to see all the children summing up to the top, but rather see only 1 of the children totals.  I have included a picture of my thought on this.  Where at the lowest level, I am looking at dirty and clean clothes, but when I roll it up, I only want to see my clothes summing up, not the dirty ones.

Thanks!!!!

[paramvir]

Aggregates ( custom and built in ) receive all the children item values as array, they don't carry row information with them, hence not possible to discriminate based on row's other cell values.

https://paramquery.com/pro/api#method-aggregate

[mercury85]

Just to be clear, we are limited to a one dimensional array of numbers associated with the column as the array identifier...  There is no way we can manipulate to a 2 dimensional array, or maybe a concatenated array to somehow throw conditions on the aggregations?

Or is there possibilities of nested grouping and details and pull values up from there?

[paramvir]

yes aggregates work on 1 dim arrays, but your message reminds me that js formulas combined with aggregates could be used for your purpose.

https://paramquery.com/pro/demos/grid_formula

I'm looking into it and would get back to you.

[mercury85]

Thanks for the quick reply, does JS formulas work on hidden columns by any chance, if that were the case, bringing in some more fields wouldn't be too bad.  I am going to look into the nested groupings, curious if we can extract numbers from the nested items.

[mercury85]

Hey there,  I was looking at ways to look at values, not sure if this would be similar.  But maybe we can inject these into rows if possible...




var data = grid.options.dataModel.data;

var TargetColumn = "Type",
TargetSummary = "[Total Wow Likes]",
SummaryList = [];

for(var i=0, len = data.length; i<len; i++){
       var rowData = data;         
           if(rowData[TargetColumn] == TargetSummary){         
               
               Object.keys(rowData).forEach(function (key) {
               if (key.match(/C.*/))
               {            
               debugger
               SummaryList.push({"Name": TargetSummary, ID: key, key: rowData[key]});   
               }        
        });
}}   

[paramvir]

JS formulas work on hidden columns too. In this plnkr example I've skipped the freight values from aggregates for customer 'Yvonne Moncada'

https://plnkr.co/edit/2P5W0d3zHfm2ytI9PL0C?p=preview

Code for actual and formula computed column.

Code: [Select]

{
    //actual column.
    hidden: true,
    dataType: "float",
    dataIndx: "Freight"
  }, {
    //computed column.
    title: "Freight",
    summary: {
      type: "sum"
    },
    width: 120,
    format: '$##,###.00',
    dataType: "float",
    dataIndx: "freight"
  },

Code for formula.

Code: [Select]

    formulas: [
      ['freight', function(rd) {
        //conditional.
        return rd.ContactName ==  'Yvonne Moncada'? undefined: rd.Freight;
      }]
    ],


[mercury85]

 

I redid what you did and noticed the behavior occurring.  At the child level, the values are removed.  It appears that this solution works but it prevents the values down to the children.  My attempt is to keep the children, and only have the subtotals impacted for totals.


One way I could see this working is where subtotals appear when collapsed, so more so a trigger effect when the rows are collapsed that the children are removed from this method.

Going to fiddle around and create a function on the formula if pq_hidden = true or undefined.  I think that may work here.

Thanks!

[paramvir]

Please check this updated plnkr. column.render has been added to the computed column to show values ( taken from original column ) at the child level.

https://plnkr.co/edit/2P5W0d3zHfm2ytI9PL0C?p=preview

Code: [Select]

{
    //computed column.
    title: "Freight",
    summary: {
      type: "sum"
    },
    width: 120,
    format: '$##,###.00',
    dataType: "float",
    dataIndx: "freight",
    render: function(ui){
      //debugger;
      var rd = ui.rowData;
      return (rd.ContactName ==  'Yvonne Moncada'?
        pq.formatNumber(rd.Freight, ui.column.format):
        ui.formatVal);
    }
  }


[mercury85]

Thanks for the quick reply there, playing around in plunker, much nicer than fiddle.  I changed things over, and am now playing with the editing portion.  Hopefully that functionality is not lost.

[mercury85]

Since we are using a calculated column:  would we be able to edit it just by using the same keys and modifying them..

Following this guide:  https://paramquery.com/demos/crud

Thinking something similar to the bottom:

change: function (evt, ui) {       
       var newKeys = ui.updateList[0].newRow;
       var index = ui.updateList[0].rowIndx;
       changeKeysToUpper(newKeys);
       
      pq.grid("updateRow", {rowIndx: index, data: newKeys});
}

After a couple of hours of failing, I threw the actual column in and hid it.  Once the column was in place, updates are working as needed.

I will post an example of this in a bit.

[paramvir]

This is an old post, but just wanted to add that rows information is also available in aggregates since version 6, that helps to simplify the solution to the use case of this thread.

https://paramquery.com/pro/api#method-aggregate