topic Re: Sorted Aggr function in App Development

Sorted Aggr function

maxsheva — Tue, 18 Dec 2018 11:16:45 GMT

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.

Re: Sorted Aggr function

rangam_s — Tue, 18 Dec 2018 11:32:21 GMT

Can you try modifying the sorting order for that object?

Re: Sorted Aggr function

maxsheva — Tue, 18 Dec 2018 11:42:56 GMT

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

Re: Sorted Aggr function

sunny_talwar — Tue, 18 Dec 2018 12:31:04 GMT

How about if you try this

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

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

Re: Sorted Aggr function

maxsheva — Tue, 18 Dec 2018 12:55:40 GMT

Thank you Sunny! As usual quick and accurate answer.

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

Re: Sorted Aggr function

sunny_talwar — Tue, 18 Dec 2018 13:03:00 GMT

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

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

Re: Sorted Aggr function

maxsheva — Tue, 18 Dec 2018 13:17:00 GMT

Thanks but the latest formula doesn't work

Re: Sorted Aggr function

sunny_talwar — Tue, 18 Dec 2018 13:34:00 GMT

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
];

Re: Sorted Aggr function

maxsheva — Tue, 18 Dec 2018 13:41:08 GMT

Thanks a lot, Sunny!