Calculated row: How to calculate cell value of the particular column based on the values from other rows in the same column using AG Grid?

Question

I want to implement a custom calculation for the specific row using the values from other rows in the same column. I found that AG Grid provides the ability to define Column Definition Expressions and aggFunc, but they don't solve what I want:

1. Column Definition Expressions (let's call it CDE) allow users to reference other columns of the same row
2. aggFunc is helpful in the case of grouping where users can use built-in functions or define custom aggregation function, which can use cell values of the same column only inside the particular group.

I need to solve the following:

Category groups         | Jan                        | Feb | Total
---------------------------------------------------------------------------------
Income                  | {it is place for aggFunc}  |     | {it is place for CDE}
    In 1                |                            |     |
    In 2                |                            |     |
    Ignored In in Net   |                            |     |
Liabilities             |                            |     |
Net Income       ???    | In 1 + In 2 - Liabilities  |     |
Net Income Total ???    | Income - Liabilities       |     |

In the result, it should be like this:

Category groups         | Jan   | Feb | Total
----------------------------------------------
Income                  | 62    |  17 |  79
    In 1                | 12    |  10 |  22
    In 2                | 20    |  5  |  25
    Ignored In in Net   | 30    |  2  |  32
Liabilities             | 15    |  10 |  25
Net Income       ???    | 17    |  5  |  22
Net Income Total ???    | 47    |  7  |  54

So, how I can define the formula (expression/aggregation function) for Net Income row and Net Income Total row by AG Grid (react), where I can refer to any needed rows within the same column (in the case above Net Income = In 1 + In 2 - Liabilities and Net Income Total = Income - Liabilities for Jan, Feb, etc.)? Which API can be used?

For now, I implemented this DEMO and I didn't find any clues in the official AG Grid documentation on how to implement the needed case and is it possible with this library at all...

UPD. I tried to implement it by:

1. Override getRowNodeId() callback to have predictable rowNodeId;
2. Get rowNode by api.getRowNode(rowId);
3. use getValue() function which has 2 parameters (colKey, rowNode) and in theory it should return the value of the target column of the specified row.

In result I have:

gridOptions = {
  getRowNodeId: row => {
    return `${row.level0}${row.lastLevel}`;
  },
  ...
  columnTypes: {
    ...
    janType: {
      enableValue: true,
      aggFunc: 'sum',
      valueGetter: ({ data, getValue, api }) => {
        if (data.lastLevel === 'Net Income') {
          const in1Row = api.getRowNode('IncomeIn 1');
          const in2Row = api.getRowNode('IncomeIn 2');
 
          // Uncaught RangeError: Maximum call stack size exceeded
          return getValue("Jan", in1Row) + getValue("Jan", in2Row);
        }
        ...
        return data.Jan;
      }
    },
    ...
  },
};

This doesn't work and getValue("Jan", in1Row) + getValue("Jan", in2Row) causes Uncaught RangeError: Maximum call stack size exceeded.

Thanks for any ideas/examples in solving this case!

Answer 1

For now, it seems that the only possible way and place to implement this is to use the onGridReady event, and there it is possible to set values for calculated rows (via rowNode.setDataValue()). The grid has all data (+ aggregated data) at this stage. This link is useful to understand how to collect all data.

The better way is to define getRowNodeId callback

getRowNodeId: row => {
   // custom rowNodeId resolver
},

and get values by data column and rowId (Jan, Feb, etc.).

Here is a simple implementation:

  onGridReady: ({ api }) => {
    const income = api.getRowNode('row-group-0');
    const in1Row = api.getRowNode('IncomeIn 1');
    const in2Row = api.getRowNode('IncomeIn 2');
    const liabilities = api.getRowNode('Liabilities');
    
    api.forEachNode(rowNode => console.log(rowNode.id));

    for (const col of ["Jan", "Feb"]) {
      const netIncome = api.getValue(col, in1Row) + api.getValue(col, in2Row) - api.getValue(col, liabilities);
      const netIncomeTotal = api.getValue(col, income) - api.getValue(col, liabilities);

      const netIncomeRow = api.getRowNode('Net Income');
      netIncomeRow.setDataValue(col, netIncome);

      const netIncomeTotalRow = api.getRowNode('Net Income Total');
      netIncomeTotalRow.setDataValue(col, netIncomeTotal);
    }
  }

Reference to plunker HERE.

Answer 2

Have you looked at Calculated Columns in AdapTable which we use in conjunction with AG Grid.

It gives you exactly what you want: https://docs.adaptabletools.com/guide/handbook-calculated-column