Aggregation in Pivot Table 2 component needs to happen by column and not one type of aggregation for all metrics.

[jgarcia-74]

A clear and concise description of what the bug is.

We have a combination of sales and percentage metrics in a grid report, which we use grouping functionality. This way, users can drill down from, let's say, Division -> Region -> District, and seeing the Sales and Margin % for each one of those groupings as they drill down. However, currently this component only allows you to specify one type of aggregation for all the metrics in the reports. This clearly is not useful. We need to have Sum aggregation for the sales metrics, and we might want to have the 'average' aggregation for the Margin (percentage) metrics. Currently, we can't display the sum for the percentage metrics, because it just doesn't make sense to do so.

Is this something that is in the roadmap to be fixed? I already modified the current Table component to allow to add hierarchical drilling and aggregation per column, however, there are some other functionality that we would like to use in pivot table 2.

How to reproduce the bug

Create a dataset that you can group by with different levels, have a sales and percentage metrics in the report, and then do the aggregation. You can select only one type of aggregation for all the metrics, instead of specifying the aggregation by column, giving us wrong results.

Expected results

I would like to specify the aggregation by column, so the totals/subtotals by grouping, show the sum for sales metrics and average for percentage metrics, for example.

Actual results

You can only select one aggregation for all metrics, which is wrong.

Environment

(please complete the following information):

superset version: 1.3.2
all browsers

Checklist

Make sure to follow these steps before submitting your issue - thank you!

• [x ] I have checked the superset logs for python stacktraces and included it here as text if there are any.
• [ x] I have reproduced the issue with at least the latest released version of superset.
• [ x] I have checked the issue tracker for the same issue and I haven't found one similar.

Additional context

Add any other context about the problem here.

[jacobcroope]

@jgarcia-74 I am experiencing the same issue. Did you ever find a workaround or implementation?

[jgarcia-74]

Yes... I had to create my own Custom aggregation function, and allowed users to select what type of aggregation they wanted to use in each specific metric. My custom aggregator looks like the following, and it works

'Custom': function () {
return function() {
return {
myNum: 0,
count: 0,
funct: 'Sum',
formatStr: '0',
numerator: 0,
denominator: 0,
oneMinus: false,
push: function(record) {
//get the info from the columnConfig
console.log("columnConfig", columnConfig)
let numCol = undefined
let denomCol = undefined
if (columnConfig === undefined) {
this.formatStr = '0'
this.funct = 'Sum'
}
else {
const metrInfo = columnConfig[record.metric]
if (metrInfo === undefined) {
this.formatStr = '0'
this.funct = 'Sum'
}
else {
if (metrInfo['format'] === undefined) {
this.formatStr = '0'
}
else {
this.formatStr = metrInfo['format']
}
if (metrInfo['aggType'] === undefined) {
this.funct = 'Sum'
}
else {
this.funct = metrInfo['aggType']
}
if (this.funct === 'SoS') {
this.oneMinus = metrInfo['oneMinus']
numCol = metrInfo['numerator']
denomCol = metrInfo ['denominator']
}
}
}
if (this.funct === 'Sum') {
this.myNum += record.value;
}
else if (this.funct === 'Average' || this.funct === 'Count') {
this.myNum += record.value;
this.count += 1;
}
else if (this.funct == 'Max') {
if (this.count == 0) {
this.myNum = record.value
this.count = 1
}
if (this.myNum < record.value) {
this.myNum = record.value
}
}
else if (this.funct == 'Min') {
if (this.count == 0) {
this.myNum = record.value
this.count = 1
}
if (this.myNum > record.value) {
this.myNum = record.value
}
}
else if (this.funct == 'SoS') {
if (numCol !== undefined && denomCol != undefined){
this.numerator += record[numCol]
this.denominator += record[denomCol]
}
}
return this.myNum;
},
value: function() {
console.log('myNum;', this.myNum)
if (this.funct === 'Average') {
return this.myNum / this.count
}
else if (this.funct === 'Count') {
return this.count
}
else if (this.funct === 'SoS') {
if (this.oneMinus) {
return 1 - (this.numerator/this.denominator)
}
else {
return this.numerator/this.denominator
}
}
return this.myNum
},
format: function(x) {
return numeral(x).format(this.formatStr);
},
};
};
},

[jacobcroope]

Still familiarizing myself with the codebase where did you implement this? I'll put this on my list of things to make a PR on. Thanks @jgarcia-74

[jgarcia-74]

I duplicated the plugin so that I used my own, and could modify the code. Then, on the main .tsx file, from the code, you will see that there is a section that defines the aggregatorsFactory json. In this, I added my own custom aggregator, which is the code that I sent you. Then, where it is defining the PivotTable, in the aggregatorName property, I am using my own. So, it says aggregatorName='Custom'.

So, the first thing you need to do is duplicate the existing component, and you can find this one under superset-ui/plugins/plugin-chart-pivot-table. I went through the process of creating a new custom plugin, and just plugged in the existing code to make it my own. Hopefully this makes sense.

[jacobcroope]

That is very helpful! Thank you.

[mtthsbrr]

This is awesome @jgarcia-74. Is it still working for v2.1.0?

Also I would be very interested how users define the numCol and denomCol for the "Sum over Sum" magic in the UI. If I'm not mistaken, this would enable one to get a weighted avg on every aggregation level in the (sub)totals - right?

[emgoryaynova]

Hi! Is there any update on this task status? Will different aggregation be added in pivot v2 without creating any custom plugin?

[goldjee]

Bumping this old discussion. I'm currently scratching my head trying to solve this problem in my Pivot tables. Is there a workaround in current version of Superset (4.0.2+)?

For Pivot table there is a mandatory selector that allows to choose aggregation function. But if your rows contain both additive measures and ratios, subtotals and totals kinda break as chosen aggregation function is applied to all the measures at once. Also as far as I see, totals are calculated after the detailed data is gathered from DB. So Pivot can't take advantage of measures definitions while calculating totals.

I've found related issue regarding this topic, but it looks like it was left hanging.

[felipegranado]

Hi!

This is a well-resolved issue in most competitors (PBI, Looker..) and this correction would be very good for all of us!

@goldjee , There's an issue related to this issue topic

@rusackas you can choose wich thread continue to this problem... thanks!