Sum only certain aggregate

[JonathanG]

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

[sunny_talwar]

May be this

Min(Aggr(
  Sum(DIFF)
, PLACE))

[JonathanG]

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

[Kushal_Chawda]

jus with little bit modification to what @sunny_talwar  suggested.

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

[JonathanG]

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?

[Kushal_Chawda]

try below

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

[JonathanG]

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

[JonathanG · Accepted solution]

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!