Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Highlighted
maxsheva
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.

Untitled.jpg

1 Solution

Accepted Solutions

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:
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
];
8 Replies
rangam_s
Contributor II

Re: Sorted Aggr function

Can you try modifying the sorting order for that object?

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

Untitled.jpg

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

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)

 

maxsheva
Contributor II

Re: Sorted Aggr function

Thanks but the latest formula doesn't work

Capture.JPG

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

Re: Sorted Aggr function

Thanks a lot, Sunny!