Need a special offer?Find out if your project fits.
+

Distinct Sum?

Answered
David Chen asked on May 4, 2016

I know there is distinct count
 
but is there distinct sum?

3 answers

Public
Iryna Kulchytska Iryna Kulchytska Flexmonster 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

Public
David Chen 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

Public
Iryna Kulchytska Iryna Kulchytska Flexmonster May 4, 2016

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

Please login or Register to Submit Answer