asked on May 4, 2016
I know there is distinct count
but is there distinct sum?
⋅ May 4, 2016
Hi David,
Thank you for your question.
Could you please provide us with the definition of distinct sum aggregation and the difference between sum aggregation and distinct sum aggregation?
Kind regards,
Iryna
⋅ May 4, 2016
let say
Item | Tracking # | $ Value
---------------|-------------
Item1| A10000 | $4
Item2|A10000 | $4
Item3|A10000 | $4
Item4|A10001 | $7
Item5|A10000 | $4
Item6|A10001 | $7
When you sum of value by tracking# is: $4 + $4 + $4 + $7 + $4 + $7 = $30
distinct sum of value by tracking # is: $4 + $7 = $11
Similar ways as count vs distinct count
Sql example will be like
Distinct Sum
SELECT SUM(SQ.COST)
FROM
(SELECT DISTINCT [Tracking #] as TRACK,[Value] as COST FROM YourTable) SQ
Sum
SELECT SUM([Value]) FROM YourTable
David,
Thank you for providing the explanations.
Unfortunately, there is no distinct sum aggregation in the component.
I can suggest you the following workaround for the case when you want to display Cost in the pivot table report - you can use average aggregation and hide grand totals, as it is done in the following sample based on the data you provided above: https://jsfiddle.net/irynakulchytska/w2pagee7/
var report = {
configuratorActive: false,
data: jsondata,
viewType: "grid",
showGrandTotals: false,
rows: [{uniqueName: "Tracking #"}],
columns: [{uniqueName: "[Measures]"}],
measures: [{uniqueName: "Value", aggregation: "average"}]
};
Will this work for you?
Kind regards,
Iryna