Pivot Table (legacy) sum issues about superset HOT 1 OPEN

To resolve the issue where the Pivot Table (legacy) in Superset displays the Max value instead of the Sum, despite selecting 'Sum' as the Aggregation Function in the Pivot Options, you should ensure that the correct aggregator is being used. Here are the steps and checks you can perform:

1. Verify the Aggregation Function Configuration:
   Ensure that the aggregateFunction property in your Pivot Table configuration is set to 'Sum'. This can be done by setting the aggregateFunction to 'Sum' in the control panel configuration.

   Example configuration:

   const pivotTableConfig = {
     controlPanelSections: [
       {
         label: t('Options'),
         expanded: true,
         controlSetRows: [
           [
             {
               name: 'aggregateFunction',
               config: {
                 type: 'SelectControl',
                 label: t('Aggregation function'),
                 clearable: false,
                 choices: [
                   ['Count', t('Count')],
                   ['Sum', t('Sum')],
                   // other choices...
                 ],
                 default: 'Sum',
                 description: t('Aggregate function to apply when pivoting and computing the total rows and columns'),
                 renderTrigger: true,
               },
             },
           ],
           // other controls...
         ],
       },
     ],
   };

2. Check for Overrides or Custom Formatters:
   Verify that there are no overrides or custom formatters affecting the aggregation. Ensure that the data being passed to the aggregator is correctly formatted.

3. Verify the aggregatorsFactory Function:
   The aggregatorsFactory function in the PivotTableChart component defines the available aggregation functions, including 'Sum'. Ensure that the 'Sum' aggregator is correctly defined and used.

   const aggregatorsFactory = (formatter: NumberFormatter) => ({
     Count: aggregatorTemplates.count(formatter),
     'Count Unique Values': aggregatorTemplates.countUnique(formatter),
     'List Unique Values': aggregatorTemplates.listUnique(', ', formatter),
     Sum: aggregatorTemplates.sum(formatter),
     Average: aggregatorTemplates.average(formatter),
     Median: aggregatorTemplates.median(formatter),
     'Sample Variance': aggregatorTemplates.var(1, formatter),
     'Sample Standard Deviation': aggregatorTemplates.stdev(1, formatter),
     Minimum: aggregatorTemplates.min(formatter),
     Maximum: aggregatorTemplates.max(formatter),
     First: aggregatorTemplates.first(formatter),
     Last: aggregatorTemplates.last(formatter),
     'Sum as Fraction of Total': aggregatorTemplates.fractionOf(
       aggregatorTemplates.sum(),
       'total',
       formatter,
     ),
     'Sum as Fraction of Rows': aggregatorTemplates.fractionOf(
       aggregatorTemplates.sum(),
       'row',
       formatter,
     ),
     'Sum as Fraction of Columns': aggregatorTemplates.fractionOf(
       aggregatorTemplates.sum(),
       'col',
       formatter,
     ),
     'Count as Fraction of Total': aggregatorTemplates.fractionOf(
       aggregatorTemplates.count(),
       'total',
       formatter,
     ),
     'Count as Fraction of Rows': aggregatorTemplates.fractionOf(
       aggregatorTemplates.count(),
       'row',
       formatter,
     ),
     'Count as Fraction of Columns': aggregatorTemplates.fractionOf(
       aggregatorTemplates.count(),
       'col',
       formatter,
     ),
   });

4. Check the transformProps Function:
   Verify the transformation of chart properties in the transformProps function to ensure that the aggregateFunction is correctly set to 'Sum'. Here is an example test case from the transformProps function:

   describe('PivotTableChart transformProps', () => {
     const setDataMask = jest.fn();
     const formData = {
       groupbyRows: ['row1', 'row2'],
       groupbyColumns: ['col1', 'col2'],
       metrics: ['metric1', 'metric2'],
       tableRenderer: 'Table With Subtotal',
       colOrder: 'key_a_to_z',
       rowOrder: 'key_a_to_z',
       aggregateFunction: 'Sum',
       transposePivot: true,
       combineMetric: true,
       rowSubtotalPosition: true,
       colSubtotalPosition: true,
       colTotals: true,
       rowTotals: true,
       valueFormat: 'SMART_NUMBER',
       metricsLayout: MetricsLayoutEnum.COLUMNS,
       viz_type: '',
       datasource: '',
       conditionalFormatting: [],
       dateFormat: '',
       legacy_order_by: 'count',
       order_desc: true,
       currencyFormat: { symbol: 'USD', symbolPosition: 'prefix' },
     };
     const chartProps = new ChartProps<QueryFormData>({
       formData,
       width: 800,
       height: 600,
       queriesData: [
         {
           data: [{ name: 'Hulk', sum__num: 1, __timestamp: 599616000000 }],
           colnames: ['name', 'sum__num', '__timestamp'],
           coltypes: [1, 0, 2],
         },
       ],
       hooks: { setDataMask },
       filterState: { selectedFilters: {} },
       datasource: { verboseMap: {}, columnFormats: {} },
       theme: supersetTheme,
     });
     it('should transform chart props for viz', () => {
       expect(transformProps(chartProps)).toEqual({
         width: 800,
         height: 600,
         groupbyRows: ['row1', 'row2'],
         groupbyColumns: ['col1', 'col2'],
         metrics: ['metric1', 'metric2'],
         tableRenderer: 'Table With Subtotal',
         colOrder: 'key_a_to_z',
         rowOrder: 'key_a_to_z',
         aggregateFunction: 'Sum',
         transposePivot: true,
         combineMetric: true,
         rowSubtotalPosition: true,
         colSubtotalPosition: true,
         colTotals: true,
         rowTotals: true,
         valueFormat: 'SMART_NUMBER',
         data: [{ name: 'Hulk', sum__num: 1, __timestamp: 599616000000 }],
         setDataMask,
         selectedFilters: {},
         verboseMap: {},
         metricsLayout: MetricsLayoutEnum.COLUMNS,
         metricColorFormatters: [],
         dateFormatters: {},
         emitCrossFilters: false,
         columnFormats: {},
         currencyFormats: {},
         currencyFormat: { symbol: 'USD', symbolPosition: 'prefix' },
       });
     });
   });

By following these steps and ensuring that the aggregateFunction is correctly set to 'Sum', you should be able to resolve the issue with the Pivot Table (legacy) displaying the Max value instead of the Sum. If the issue persists, please provide additional details or screenshots of your configuration and data to further diagnose the problem.

To continue the conversation, mention @dosu.

from superset.