How to display top 10 based on the calculated field (percentage of Grand Total)

[Zahid Ansari]

Hi,

Anybody out here to help me on how to display top 10 in the Pivot Table based on the calculated field (percentage of Grand Total) 

Thanks!

[AngelinFaithSheeba PaulvannanRajadurai]

Hi Zahid,

We would like to inform you that our Syncfusion Pivot Table's calculated field currently only allows you to create formulas with fields using the aggregate types listed below when creating calculated fields in the pivot table. Therefore, we regret that we are unable to create a calculated field with the aggregate type "Percentage of Grand Total".

 Supported aggregation types for calculated field:

• Sum
• Count
• Distinct Count
• Product
• Min
• Max
• Avg
• Median
• PopulationVar
• SampleVar
• PopulationStDev
• SampleStDev

Please find the UG document link below to know more about supported operators and functions for the calculated field formula below,

UG Document: https://ej2.syncfusion.com/javascript/documentation/pivotview/calculated-field/#supported-operators-and-functions-for-the-calculated-field-formula.

Moreover, currently we don’t have any built-in option for displaying the top 10 values in the pivot table. Therefore, we regret to inform you that your requirement to display top 10 values using the calculated field is not possible in our pivot table.

Regards,

Angelin Faith Sheeba.

[Zahid Ansari]

Thanks Sheeba,

You didn't get my point. The question is how can I filter the Pivot View to display the top 10 records out of 1000 during runtime. 

That is, when the Pivot View is initialized total rows from the data source is 1000, but if I want to display only the top 10 rows sorted on the calculated field, how can I do that.

So the 2 question remains unanswered:

1) how to sort(asc/desc) rows on the calculated field from code (refer to the below screenshot columns: Count/Consume Price/Grand total Percen

2) how to display only the top 10 rows based on the sorted order

Please refer to the below screenshot:

[AngelinFaithSheeba PaulvannanRajadurai]

Hi Zahid,

We would like to let you know that the currently we don’t have a built-in option to filter the top 10 values. However, you can able to achieve this by applying the value sort to the specific column at code-behind using the valueSortSettings property and then include the top 10 row headers in the filterSettings to display the top 10 members in the pivot table. In the below code example, we have applied value sorting for the “Sold Amount” values  under the “FY 2015” column header using valueSortSettings property and then added the first two row headers(from country field) in the “filterSettings” to display the top 10 values based on your requirement.

Code example:

var pivotObj = new ej.pivotview.PivotView({

        dataSourceSettings: {

           // Apply value sorting here.

            valueSortSettings: {

                headerText: 'FY 2015##Sold Amount',

                headerDelimiter: '##',

                sortOrder: 'Descending'

            },

        },  

        enginePopulated(args) {

            var count =0;

            if(isSort){

                for (var i = 0; i < args.pivotValues.length; i++) {

                    for (var j = 0; args.pivotValues[i] != null && j < args.pivotValues[i].length; j++) {

                        if (args.pivotValues[i][j] != null && args.pivotValues[i][j].axis == 'row' ){

                            // Here you can display the top 10 values based on your requirement.

                            // Moreover, you can change the top values here.

                            if(count < 10){

                                if(args.pivotValues[i][j].valueSort.axis == "Country" && count == 0){

                                    pivotObj.dataSourceSettings.filterSettings = [{name: 'Country', type: 'Include', items: [args.pivotValues[i][j].actualText]}];

                                } else {

                                    for (var l=0; l<pivotObj.dataSourceSettings.filterSettings.length; l++) {

                                        if(pivotObj.dataSourceSettings.filterSettings[l].name == "Country"){

                                            pivotObj.dataSourceSettings.filterSettings[l].items.push(args.pivotValues[i][j].actualText)

                                        }

                                    }

                                }

                                count++;

                            }

                        }

                    }

                }

                isSort = false;

            }

        },

}

Output screenshot:

To know more about value sorting, please refer to the document link below.

Document: https://ej2.syncfusion.com/javascript/documentation/pivotview/sorting#value-sorting

Meanwhile, we have prepared a sample for your reference.

Sample: https://stackblitz.com/edit/sulunf-gyqfxw?file=index.js

Regards,

Angelin Faith Sheeba.