Open theflasherman opened 3 years ago
Hey, thanks for reporting this! You are absolutely correct.
The value we are showing is the average conversion per item (avg(sum(sales/downloads))), not the total conversion for each period (sum(sales)/sum(downloads)).
In Excel there is a way to calculate fields based on individual items (which is what we are doing here) or on group aggregates (which we do not support).
We are updating the sample to be a correct calculated field example.
Hi Chris
Thanks for getting back to me on this.
It’s a shame that the Pivot Grid / Chart do not support aggregate calculations. We have a use case where we calculate Return on Investment. This is not additive from the record level up. However, if we have Sum(return) and Sum(investment) we can calculate these easily in the pivot grid.
I did have a work around on another case for weighted average days. sender = an ODataCollectionView. This worked but when trying to use it in a larger data set (20k rows) it proved very slow and the browser would hang.
aggregate: 'Sum',
getValue(item) {
return (item.Notional / wijmo.getAggregate(wijmo.Aggregate.Sum, sender.items.filter(d => d.Division === item.Division), 'Notional')) * item.MaturityDays;
},
So, if you could implement this feature it would be very welcome here.
Kind regards
Brian
From: Chris Bannon notifications@github.com Sent: 08 February 2021 19:50 To: GrapeCity/wijmo-demo wijmo-demo@noreply.github.com Cc: theflasherman bfisher@gcma.co.uk; Author author@noreply.github.com Subject: Re: [GrapeCity/wijmo-demo] Discussion on #OLAP/PivotPanel/Fields/Customize/FieldSettingsDialog/Calculated (#5)
Hey, thanks for reporting this! You are absolutely correct.
The value we are showing is the average conversion per item (avg(sum(sales/downloads))), not the total conversion for each period (sum(sales)/sum(downloads)).
In Excel there is a way to calculate fields based on individual items (which is what we are doing here) or on group aggregates (which we do not support).
We are updating the sample to be a correct calculated field example.
— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/GrapeCity/wijmo-demo/issues/5#issuecomment-775399823 , or unsubscribe https://github.com/notifications/unsubscribe-auth/AC4LTGFTFWUUUY4PZPTNI6TS6A6ABANCNFSM4XH5AUTA . https://github.com/notifications/beacon/AC4LTGE7YO3DIWBFW4UWZALS6A6ABA5CNFSM4XH5AUTKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOFY32TDY.gif
Hey Brian, we have corrected this sample and it will be live in mid-March.
More importantly, we have added a getAggregateValue as an alternative to getValue. I think this will be exactly what you need. Here is an example of usage:
let ng = new PivotEngine({
autoGenerateFields: false,
itemsSource: getData(10000),
showColumnTotals: 'GrandTotals',
showRowTotals: 'Subtotals',
fields: [
{ binding: 'product', header: 'Product' },
{ binding: 'date', header: 'Date', format: 'yyyy \"Q\"q' },
{
header: 'Range',
dataType: 'String',
aggregate: 'Cnt',
// use getValue to calculate the sales range (High, Medium, or Low)
getValue: (item) => {
let sales = item.sales;
return sales <= 13 ? 'Low' : sales >= 17 ? 'High' : 'Medium';
}
},
{ binding: 'sales', header: 'Sales', format: 'n0' },
{ binding: 'downloads', header: 'Downloads', format: 'n0' },
{
header: 'Conversion',
dataType: 'Number',
format: 'p0',
// getAggregateValue computes an aggregate from a summary row (Sales/Downloads)
getAggregateValue: row => row.Downloads ? row.Sales / row.Downloads : 0
}
],
rowFields: ['Date', 'Range'],
valueFields: ['Sales', 'Downloads', 'Conversion']
});
This is in our nightly build already if you want to give it a try now!
Hi Chris
Thanks for doing putting this in. I’ve tested it and it is exactly what I’m looking for! This is absolutely brilliant and I really appreciate you adding this so fast.
One thing I noticed about the syntax is that the getValue uses the binding names, whereas the getAggregateValue uses the header names, e.g. downloads vs Downloads. Is this intentional? It’s not a biggie as I’m over the moon to have the functionality.
Kind regards
Brian
From: Chris Bannon notifications@github.com Sent: 12 February 2021 15:29 To: GrapeCity/wijmo-demo wijmo-demo@noreply.github.com Cc: theflasherman bfisher@gcma.co.uk; Author author@noreply.github.com Subject: Re: [GrapeCity/wijmo-demo] Discussion on #OLAP/PivotPanel/Fields/Customize/FieldSettingsDialog/Calculated (#5)
Hey Brian, we have corrected this sample and it will be live in mid-March.
More importantly, we have added a getAggregateValue as an alternative to getValue. I think this will be exactly what you need. Here is an example of usage:
let ng = new PivotEngine({
autoGenerateFields: false,
itemsSource: getData(10000),
showColumnTotals: 'GrandTotals',
showRowTotals: 'Subtotals',
fields: [
{ binding: 'product', header: 'Product' },
{ binding: 'date', header: 'Date', format: 'yyyy \"Q\"q' },
{
header: 'Range',
dataType: 'String',
aggregate: 'Cnt',
// use getValue to calculate the sales range (High, Medium, or Low)
getValue: (item) => {
let sales = item.sales;
return sales <= 13 ? 'Low' : sales >= 17 ? 'High' : 'Medium';
}
},
{ binding: 'sales', header: 'Sales', format: 'n0' },
{ binding: 'downloads', header: 'Downloads', format: 'n0' },
{
header: 'Conversion',
dataType: 'Number',
format: 'p0',
// getAggregateValue computes an aggregate from a summary row (Sales/Downloads)
getAggregateValue: row => row.Downloads ? row.Sales / row.Downloads : 0
}
],
rowFields: ['Date', 'Range'],
valueFields: ['Sales', 'Downloads', 'Conversion']
});
This is in our nightly build already if you want to give it a try now!
— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/GrapeCity/wijmo-demo/issues/5#issuecomment-778262359 , or unsubscribe https://github.com/notifications/unsubscribe-auth/AC4LTGBHWR45SINNETPJJNTS6VCLNANCNFSM4XH5AUTA . https://github.com/notifications/beacon/AC4LTGF5TLWMSOATPMMC5B3S6VCLNA5CNFSM4XH5AUTKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOFZRVOVY.gif
Hi Chris
Further to the above, if I remove 'Sales' and 'Downloads' from the valueFields then the 'Conversion' doesn't calculate. My use case here is for a pivot chart on the pivotEngine where I only want to show the 'Conversion' values as the 'Sales' and 'Downloads' numbers are on a different scale. I think this may make it more complex to implement so sorry for being a pain.
Kind regards Brian
Hey Brian, thanks for trying (and suggesting) the new feature!
As for the two points you made...
Thanks again for reporting the issue and making the great suggestion! We plan to release it next month.
The Calculated Fields is wrong for this. The Conversion rate should be for the summary displayed, not an average of the underlying individual items.
OLAP/PivotPanel/Fields/Customize/FieldSettingsDialog/Calculated