Posted 28 November 2018, 8:20 pm EST
Hi,
I am trying to calculate the true weighted average
True Weighted Average in PivotGrid
Posted by: baane on 28 November 2018, 8:20 pm EST
-
baane
-
baane
Posted 28 November 2018, 8:21 pm EST
Hi,
I am trying to calculate the true weighted average (https://www.wikihow.com/Calculate-Weighted-Average) in PivotGrid and cannot make it work. Here is a very simple example having only 2 rowshttps://jsfiddle.net/hop1mcyu/
{date: new Date(2018, 11, 10), id: 'SX', bucket: '[30,40]', period: 'IntraDay', ownquantity:50, volume:1000} {date: new Date(2018, 11, 10), id: 'SX', bucket: '[30,40]', period: 'IntraDay', ownquantity:100, volume:200}When pivoting the data, I am trying to get the weighted average calculated as:
(50 + 100) / (1000+200) = 0.125This is in pivotgrid.js referred to as Sum over Sum aggregation. (https://jsfiddle.net/nicolaskruchten/kn381h7s/)
At the moment I am only able to get follwing calculations using either the getValue function or by setting the weightField (https://demos.wijmo.com/5/Angular/WijmoHelp/WijmoHelp/topic/wijmo.olap.PivotField.Class.html#weightField). Both can give me follwing output which is not really a true weighted average as they are aggregating over rows and not columns:
Avg((50 / 1000) + (100/200)) = ((50 / 1000) + (100/200))/2 = 0.275Being able to achieve this would be greatly enhancing the functionality of the pivotgrid implementation.
Thanks
-
sharad.tomer
Posted 2 December 2018, 8:07 pm EST
Hi,
Currently, custom aggregates are not supported in the PivotEngine. Hence we have created an enhancement request for the same(Internal tracking Id: 356062). We will let you know about any updated regarding this.
Till then you may use itemFormatter to display custom aggregate values in the PivotGrid. Please refer to the following sample which demonstrates the same:
https://jsfiddle.net/2samx3qv/~Sharad
-
mavensupport
Posted 5 December 2018, 7:57 pm EST
Thats awesome! Thanks
On a related note I tried to get the same weighted averages when the same data is in flat structure i.e.:
data.push({date: new Date(2018, 11, 10), id: 'SX', bucket: '[30,40]', period: 'IntraDay', group:'own', volume:50}) data.push({date: new Date(2018, 11, 10), id: 'SX', bucket: '[30,40]', period: 'IntraDay', group:'market', volume:1000}) data.push({date: new Date(2018, 11, 10), id: 'SX', bucket: '[30,40]', period: 'IntraDay', group:'own', volume:100}) data.push({date: new Date(2018, 11, 10), id: 'SX', bucket: '[30,40]', period: 'IntraDay', group:'market', volume:200})But could not make it work. Is there any way to get the weighted averages when the data is flat? I created a jsfiddle with the data here
https://jsfiddle.net/xzk2f5e7/1/
Thanks again
-
sharad.tomer
Posted 6 December 2018, 4:17 pm EST
Everything in the fiddle seems to working as intended.
However, there could be one small issue in getCustomMsAvg() method,
function getCustomMsAvg(data){
let msSum = wijmo.getAggregate(wijmo.Aggregate.Sum, data, ‘volume’);
let msWeightSum = wijmo.getAggregate(wijmo.Aggregate.Sum, data, ‘volume’);
return msSum/msWeightSum;
}
In above method, msSum and msWeightSum are calculated from the same data set with same settings, so they will always return the same values and hence msSum/msWeightSum would always be 1 i.e. 100%. Instead, a more reasonable way of calculating msWeightSum would be using the complete dataSet instead of just details data(Modified sample with this approach: https://jsfiddle.net/warxcdey/).
If this(calculating both msSum/msWeightSum using same detail dataSet) is the intended behaviour then please let us know some more about your expectations/issue. -
mavensupport
Posted 6 December 2018, 6:48 pm EST
Thanks for the quick reply. I did not make it clear in my message what I am trying to accomplish. The data is the same as in the original example just that the data is flat (split into ‘group’ variable).
original data:
{date: new Date(2018, 11, 10), id: 'SX', bucket: '[30,40]', period: 'IntraDay', ownvolume:50, marketvolume:1000} {date: new Date(2018, 11, 10), id: 'SX', bucket: '[30,40]', period: 'IntraDay', ownvolume:100, marketvolume:200}my data:
data.push({date: new Date(2018, 11, 10), id: 'SX', bucket: '[30,40]', period: 'IntraDay', group:'own', volume:50}) data.push({date: new Date(2018, 11, 10), id: 'SX', bucket: '[30,40]', period: 'IntraDay', group:'market', volume:1000}) data.push({date: new Date(2018, 11, 10), id: 'SX', bucket: '[30,40]', period: 'IntraDay', group:'own', volume:100}) data.push({date: new Date(2018, 11, 10), id: 'SX', bucket: '[30,40]', period: 'IntraDay', group:'market', volume:200})I would like to create a pivottable to get the same ‘mS’ result as in your original solution (https://jsfiddle.net/2samx3qv/) i.e. 12.5%. The difference is that ‘volume’ is now split into different rows with groups ‘own’ and ‘market’. Where in the old example ‘ownvolume’ and ‘marketvolume’ have been in the same row (see original data above) and therefore could use getCustomMsAvg as you suggested. Basically the calculation I am trying to achieve here is:
(50 + 100) / (1000 + 200) = sum(volume[‘group’=‘own’]) / sum(volume[‘group’=‘market’]) = .125
Thanks
-
sharad.tomer
Posted 9 December 2018, 4:31 pm EST
In this case, we need to first split the data into ‘market’ and ‘own’ group and then calculate the avg.
Please refer to the following code snippet and updated sample:function getCustomMsAvg(data){ let ownGroupData = data.filter(data => data.group == 'own'); let marketGroupData = data.filter(data => data.group == 'market'); let ownSum = wijmo.getAggregate(wijmo.Aggregate.Sum, ownGroupData, 'volume'); let marketSum = wijmo.getAggregate(wijmo.Aggregate.Sum, marketGroupData, 'volume'); return ownSum/marketSum; }https://jsfiddle.net/e1n3bwjp/
~Sharad
