Sorted Aggr function

[maxsheva]

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.

[rangam_s]

Can you try modifying the sorting order for that object?

[maxsheva]

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)?

[sunny_talwar]

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))

[maxsheva]

Thank you Sunny! As usual quick and accurate answer.

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

[sunny_talwar]

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

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

[maxsheva]

Thanks but the latest formula doesn't work

[sunny_talwar · Accepted solution]

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:
LOAD *,
	 Region&'_'&State as Region_State;
LOAD * INLINE [
    Region, State, Sales
    South, Virginia, 7357994
    South, Florida, 7820644
    South, Georgia, 4127387
    West, Washington, 9651689
    West, California, 39974313
    West, Arizona, 3375967
];

[maxsheva]

Thanks a lot, Sunny!