Pivot table - create new column with formula

Pivot table - create new column with formula

Posted by: mehmetkaya on 11 December 2023, 2:47 am EST

• 

  mehmetkaya

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 11 December 2023, 2:47 am EST

  Hİ,

  I want to create an extra column in my pivot table. Let my column have a formula and be created based on other columns.

  For example I have debit column and credit column.
  
  I want to create new column balance = debit - credit

• 

  mehmetkaya

  • Post Options:
  • Link
    Copy

  Posted 11 December 2023, 10:40 am EST

  please can you answer with vue : )

• 

  anirudh.verma

  

  • Post Options:
  • Link
    Copy

  Posted 11 December 2023, 8:56 pm EST

  Hi,

  To achieve the desired functionality please add a custom field in fields array. The getAggregateValue of this field will then be used to calculate the value based on the other two columns present in the PivotGrid.

  Please refer to this code snippet for reference:

   fields: [
          { binding: 'product', header: 'Product' },
          { binding: 'date', header: 'Date', format: 'yyyy "Q"q' },
          { binding: 'sales', header: 'Sales', format: 'n0' },
          { binding: 'expenses', header: 'Expenses', format: 'n0' },
          {
            header: 'Balance',
            dataType: 'Number',
            aggregate: 'Sum',
            format: 'c0',
  
            getAggregateValue: (item) => {
              return item.Sales - item.Expenses;
            },
          },
        ],

  Please refer to this API link for more information on getAggregateValue property:
  
  https://developer.mescius.com/wijmo/api/classes/wijmo_olap.pivotfield.html#getaggregatevalue

  You may also use the getValue property of the PivotField to do some calculations based on the raw data item. Please refer to this API link for more information on getValue property: https://developer.mescius.com/wijmo/api/classes/wijmo_olap.pivotfield.html#getvalue

  I have prepared a sample in Vue demonstrating the same: https://stackblitz.com/edit/vue-example-1-sr8jpz

  Regards