Discussion on #OLAP/PivotPanel/Fields/Customize/FieldSettingsDialog/Calculated

[theflasherman]

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

[banzor]

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.

[theflasherman]

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 <#5 (comment)> , or unsubscribe <https://github.com/notifications/unsubscribe-auth/AC4LTGFTFWUUUY4PZPTNI6TS6A6ABANCNFSM4XH5AUTA> . <https://github.com/notifications/beacon/AC4LTGE7YO3DIWBFW4UWZALS6A6ABA5CNFSM4XH5AUTKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOFY32TDY.gif>

[banzor]

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!

[theflasherman]

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 <#5 (comment)> , or unsubscribe <https://github.com/notifications/unsubscribe-auth/AC4LTGBHWR45SINNETPJJNTS6VCLNANCNFSM4XH5AUTA> . <https://github.com/notifications/beacon/AC4LTGF5TLWMSOATPMMC5B3S6VCLNA5CNFSM4XH5AUTKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOFZRVOVY.gif>

[theflasherman]

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

[banzor]

Hey Brian, thanks for trying (and suggesting) the new feature!

As for the two points you made...

1. That is true. getValue works on the raw data items, so it uses the binding names. getAggregateValues works on aggregated row items, so it uses field captions instead of bindings.
2. That is a design limitation (for good performance). getAggregateValues works on aggregated row items, so it can only use fields that are present in the output view. If you don’t want to show some of those fields on the grid, you could set their width property to zero.

Thanks again for reporting the issue and making the great suggestion! We plan to release it next month.