Sum excluding top 5 values

[boorgura]

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.

[#2 · Accepted solution]

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

[boorgura]

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.