Qlik Sense App Development

Highlighted
Contributor II

Sorted Aggr function

Hi,

I am trying to get a sort order in Aggr function.

Please take a look at an example below.

This table is sorted by Sum(Sales). I need to present all aggregated values per Region as a first row per each distinct value from this first column.

I have already tried this Aggr(Sum(Sales),(Region,(=Sum({1}Sales),Desc))) but no success.

Version of Qlik Sense Desktop is 12.36.1

Any help will be appreciated. 1 Solution

Accepted Solutions MVP

Re: Sorted Aggr function

Yup, gave it a shot... I don't think sorting can help here... the only other option is to create a new field which combines Region and State into a single field and use it in a set analysis...

`Only({<Region_State = {"=Aggr(Max(TOTAL <Region> Aggr(Sum(Sales), Region_State, Region)), Region_State, Region) = Sum(Sales)"}>} Aggr(NODISTINCT Sum(Sales), Region))`

Here Region_State is created in the script like this

```Table:
Region&'_'&State as Region_State;
Region, State, Sales
South, Virginia, 7357994
South, Florida, 7820644
South, Georgia, 4127387
West, Washington, 9651689
West, California, 39974313
West, Arizona, 3375967
];```
8 Replies
Contributor II

Re: Sorted Aggr function

Can you try modifying the sorting order for that object?

Contributor II

Re: Sorted Aggr function

Hi,  thanks for suggestion.

Just tried again and found result very close to expected.

But still wondering whether is possible to present values for South with the same row as highest Sales in this Region (Florida)?  MVP

Re: Sorted Aggr function

How about if you try this

`If(Max(TOTAL <Region> Aggr(Sum(Sales), Region, State)) = Sum(Sales), Sum(TOTAL <Region> Sales))`

or

`If(Max(TOTAL <Region> Aggr(Sum(Sales), Region, State)) = Sum(Sales), Aggr(NODISTINCT Sum(Sales), Region))`
Contributor II

Re: Sorted Aggr function

Thank you Sunny! As usual quick and accurate answer.

I am wondering whether If statement will affect on performance if there 10 millions rows? MVP

Re: Sorted Aggr function

It probably will have some impact... but can you give this a try

`Aggr(Aggr(Sum(Sales), Region, (State, (=Sum({1}Sales), Desc))), Region)`

Contributor II

Re: Sorted Aggr function

Thanks but the latest formula doesn't work  MVP

Re: Sorted Aggr function

Yup, gave it a shot... I don't think sorting can help here... the only other option is to create a new field which combines Region and State into a single field and use it in a set analysis...

`Only({<Region_State = {"=Aggr(Max(TOTAL <Region> Aggr(Sum(Sales), Region_State, Region)), Region_State, Region) = Sum(Sales)"}>} Aggr(NODISTINCT Sum(Sales), Region))`

Here Region_State is created in the script like this

```Table:
Region&'_'&State as Region_State;