Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- New to Qlik Analytics
- :
- Sum only certain aggregate

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

JonathanG

Contributor II

2020-08-28
01:58 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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,654 Views

1 Solution

Accepted Solutions

JonathanG

Contributor II

2020-08-29
01:54 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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!

1,557 Views

7 Replies

sunny_talwar

MVP

2020-08-28
03:39 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

May be this

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

1,631 Views

JonathanG

Contributor II

2020-08-28
04:06 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

1,622 Views

Kushal_Chawda

MVP

2020-08-28
04:41 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

jus with little bit modification to what @sunny_talwar suggested.

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

1,606 Views

JonathanG

Contributor II

2020-08-28
04:56 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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?

1,603 Views

Kushal_Chawda

MVP

2020-08-28
07:39 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

try below

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

1,595 Views

JonathanG

Contributor II

2020-08-29
10:05 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

1,569 Views

JonathanG

Contributor II

2020-08-29
01:54 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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!

1,558 Views

Community Browser