Pivot Table (legacy) sum issues

konk0n commented 5 months ago

Bug description

In Pivot Table (legacy) instead of the Sum of the columns and rows I get the Max although I have chosen the Aggregation Function as 'Sum' in the Pivot Options of the table. I use a PostgreSQL dataset. How can I solve this issue?

Screenshot 2024-05-21 105704 Screenshot 2024-05-21 105826

How to reproduce the bug

Add a Pivot Table (legacy) as a chart
Chose Sum as Aggregation Function in the Pivot Options.
Update the Chart.

Screenshots/recordings

Screenshot 2024-05-21 105704 Screenshot 2024-05-21 105826

Superset version

master / latest-dev

Python version

3.9

Node version

16

Browser

Firefox

Additional context

No response

Checklist

[X] I have searched Superset docs and Slack and didn't find a solution to my problem.
[X] I have searched the GitHub issue tracker and didn't find a similar bug report.
[X] I have checked Superset's logs for errors and if I found a relevant Python stacktrace, I included it here as text in the "additional context" section.

dosubot[bot] commented 5 months ago

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:

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...
     ],
   },
 ],
};

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.

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,
 ),
});

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.

#### Details

**Sources** - [superset-frontend/plugins/plugin-chart-pivot-table/src/react-pivottable/utilities.js](https://github.com/apache/superset/blob/master/superset-frontend/plugins/plugin-chart-pivot-table/src/react-pivottable/utilities.js) - [superset-frontend/plugins/plugin-chart-pivot-table/src/plugin/controlPanel.tsx](https://github.com/apache/superset/blob/master/superset-frontend/plugins/plugin-chart-pivot-table/src/plugin/controlPanel.tsx) - [superset-frontend/plugins/plugin-chart-pivot-table/src/PivotTableChart.tsx](https://github.com/apache/superset/blob/master/superset-frontend/plugins/plugin-chart-pivot-table/src/PivotTableChart.tsx) - [superset-frontend/plugins/plugin-chart-pivot-table/test/plugin/transformProps.test.ts](https://github.com/apache/superset/blob/master/superset-frontend/plugins/plugin-chart-pivot-table/test/plugin/transformProps.test.ts) - [superset-frontend/plugins/plugin-chart-pivot-table/src/plugin/transformProps.ts](https://github.com/apache/superset/blob/master/superset-frontend/plugins/plugin-chart-pivot-table/src/plugin/transformProps.ts)
**About Dosu** This response is meant to be useful and save you time. It is **not meant to be a precise solution**, but rather a starting point for your own research. Help me be more useful! Please leave a 👍 if this is helpful and 👎 if it is irrelevant.

To continue the conversation, mention @dosu.

rusackas commented 5 months ago

I hope someone here can help, but we no longer actively support the older (legacy) Pivot Table.

apache / superset