Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
JonathanG
Contributor II
Contributor II

Sum only certain aggregate

Hi Guys,

There are such data:

PLACEREGIONVALUE1VALUE2DIFF
1A231
1B75-2
1C572
1D84-4
2E95-4
2F42-2
2G75-2
2H572
3I891
3J022
3K550

 

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
JonathanG
Contributor II
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!

View solution in original post

7 Replies
sunny_talwar

May be this

Min(Aggr(
  Sum(DIFF)
, PLACE))
JonathanG
Contributor II
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

Kushal_Chawda

jus with little bit modification to what @sunny_talwar  suggested.

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

JonathanG
Contributor II
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?

Kushal_Chawda

try below

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

JonathanG
Contributor II
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

JonathanG
Contributor II
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!