Announcements
cancel
Showing results for
Did you mean:
Contributor II

## Sum only certain aggregate

Hi Guys,

There are such data:

 PLACE REGION VALUE1 VALUE2 DIFF 1 A 2 3 1 1 B 7 5 -2 1 C 5 7 2 1 D 8 4 -4 2 E 9 5 -4 2 F 4 2 -2 2 G 7 5 -2 2 H 5 7 2 3 I 8 9 1 3 J 0 2 2 3 K 5 5 0

How to get the highest negative value of "Place"? so the result will be -8 (-4+(-2)+(-2)) (Place "2").

Thankyou

1 Solution

Accepted Solutions
Contributor II
Author

Thank God, I found it!!

The point is I have to aggregate my calculation based on smallest area (region), give condition, sum it, aggregate again based on bigger area (place), and then get the minimum value

so the expression is:

min(aggr(sum(if(aggr(sum(value1)-sum(value2),region)<0,aggr(sum(value1)-sum(value2),region),0)),place))

Thankyou Sunny and Kush!

7 Replies
MVP

May be this

``````Min(Aggr(
Sum(DIFF)
, PLACE))``````
Contributor II
Author

Thankyou Sunny, it returns Place "2" but with value = -6 (included positive value). I just need the highest sum of negative value (-8) and ignore the positive ones

jus with little bit modification to what @sunny_talwar  suggested.

=min(aggr(sum({<DIFF={"<0"}>}DIFF),PLACE))

Contributor II
Author

Sorryy, i forgot to mention

Actually Value 1 and Value 2 are group of value. and DIFF is measure

so i put expression sum(value2)-sum(value1) to get DIFF calculation

when i put it into your expression =min(aggr(sum({<sum(Value2)-sum(Value1)={"<0"}>}sum(value2)-sum(value1)),PLACE)), it returns "-" in my KPI

did i miss something?

try below

min(aggr(if(sum(Value2)-sum(Value1)<0,sum(value2)-sum(value1)),PLACE))

Contributor II
Author

Thankyou Kush, but it still returns to -6 😞

I think it still aggregates the every value of "Place", but i have no idea to solve it

Help

Contributor II
Author

Thank God, I found it!!

The point is I have to aggregate my calculation based on smallest area (region), give condition, sum it, aggregate again based on bigger area (place), and then get the minimum value

so the expression is:

min(aggr(sum(if(aggr(sum(value1)-sum(value2),region)<0,aggr(sum(value1)-sum(value2),region),0)),place))

Thankyou Sunny and Kush!