Announcements
Product Release Webinar: Qlik Insider airing December 6! REGISTER TODAY!
cancel
Showing results for
Did you mean:
Specialist

## Sum excluding top 5 values

Hi folks,

I am trying to create a text box with the value to show the sum, excluding the top 5 numbers.

I am able to achieve this in a chart, as I was able to limit the dimension based on the condition.

Now, I am trying to show that value in a text object.

To explain further, I have a dimension say X and value - 'val'.

I need to show sum(val) for all except top 5 X.

Any help would be highly appreciated.

1 Solution

Accepted Solutions
Not applicable

Hi there, you can limit the number of items of X dimension using set analysis, like this:

=sum({\$<X = {"=rank(aggr(sum(Val),X),4,1) > 5"}>} Val)

Notice, in this particular example the aggr within the set analsys is not needed, however i wrote it like that, in case you need to calculate the rank based on more dimension.

See attached example.

Regards

2 Replies
Not applicable

Hi there, you can limit the number of items of X dimension using set analysis, like this:

=sum({\$<X = {"=rank(aggr(sum(Val),X),4,1) > 5"}>} Val)

Notice, in this particular example the aggr within the set analsys is not needed, however i wrote it like that, in case you need to calculate the rank based on more dimension.

See attached example.

Regards

Specialist
Author

Thanks Ivan.

It actually works.

I was almost at a similar point but was doing something wrong - that I was missing out one value.

I appreciate your time and help.

Community Browser