Is it possible to implement something like a "Distinct Sum" ??

[Thierry Tremblay]

Hi,

 In our requirement we need to do Sum per grouping with the appropriate rolled-up values. Something like the Distinct Count but with a Distinct Sum

Here is a short video of our use case. - https://share.kohezion.com/p9ueZwRK

Any suggestions would be appreciated.

[AngelinFaithSheeba PaulvannanRajadurai]

Hi Thierry,

Based on your need, we currently do not have a direct option to display “DistinctSum” aggregation in the pivot table. However, using the aggregateCellInfo event, you can modify the pivot table values to meet your needs. Please see the code sample below.

In this code example, we modified the subtotal position to "bottom" in order to calculate the values from their children, because if we have the subtotal at the top, the child values will not be obtained while the subtotal row occur. As a result, we recommend that you set the "subTotalsPosition" property to "Bottom" to meet your needs.

Code example:

       dataSourceSettings: {

            // Change the sub total position to “Bottom” Position.

            subTotalsPosition: 'Bottom'

        },

        aggregateCellInfo: function(args){

            // Customize the value cells of “Sold” field only.

            if(args.fieldName == "Sold"){

                // Add the total of the child value cells here.

                if(args.row.level == 1 && args.rowCellType == "value"){

                    total += args.value;

                }

                // Assign the calculated total to the sub total here.

                else if(args.row.isSum){

                    args.value = total;

                    total = 0;

                }

            }

        }

Output screenshot:

Meanwhile we have prepared a sample with one lakh data for your reference:

Sample: https://stackblitz.com/edit/pjvqfg-mc13db?file=index.js,index.html

Please let us know if any concern occurs.

Regards,

Angelin Faith Sheeba.

[Thierry Tremblay]

Thank you, this is great. a Quick follow-up question:

Is it possible to create a 'Custom' aggregate function that could be re-used for any numeric field in the pivot table?

[AngelinFaithSheeba PaulvannanRajadurai]

Hi Thierry,

Yes, you can add custom aggregate types to the pivot table's aggregate menu using the dataBound event. Then, using the aggregateCellInfo event in the pivot table and selecting the appropriate custom aggregate type, you can do your own custom aggregation on the pivot table values. Please refer the below code example.

Code example:

dataBound: function () {

    // Add the custom aggregte types in the aggregate menu here.

    pivotObj.getAllSummaryType = function () {

      return [

        'Sum',

        'Count',

        'DistinctCount',

        'Avg',

        'Max',

        'CustomAggregateType1',

        'CustomAggregateType2',

      ];

    };

    pivotObj.pivotFieldListModule.aggregateTypes = [

      'Sum',

      'Count',

      'DistinctCount',

      'Avg',

      'Max',

      'CustomAggregateType1',

      'CustomAggregateType2',

    ];

    pivotObj.pivotFieldListModule.getAllSummaryType = function () {

      return [

        'Sum',

        'Count',

        'DistinctCount',

        'Avg',

        'Max',

        'CustomAggregateType1',

        'CustomAggregateType2',

      ];

    };

  },

  aggregateCellInfo: function (args) {

    console.log(args.aggregateType);

    // Code the logic for custom aggregation here.

    // Here we have write "count" and "Max" aggregation logic for "Sold" value field.

    if (args.fieldName === 'Sold' && args.aggregateType === 'Count') {

      args.value = args.cellSets.length;

    } else if (args.fieldName === 'Sold' && args.aggregateType === 'Max') {

      if (args.row.level == 1 && args.rowCellType == 'value') {

        total += args.value;

      } else if (args.row.isSum) {

        args.value = total;

        total = 0;

      }

    }

  },

});

 

Output screenshot:

Meanwhile we have prepared a sample for your reference:

Sample: https://stackblitz.com/edit/h24fkx-d9rwvx?file=index.js,index.html

We believe that the above provided information would fulfill your needs.

Please let us know if any concern occurs.

Regards,

Angelin Faith Sheeba.

[Thierry]

Great! Thank you very much

[Vinothkumar Yuvaraj]

Hi Thierry,

Thanks for the update. Please contact us if you have any other queries. We are always happy to assist you.

Regards,

Angelin Faith Sheeba.