How to use rank to get bottom 5

[annabergendal]

In my pivot table, I have used calulated dimension to get top 5 of a sum in a variable, like this:

if(aggr(rank($(vActiveProductsR12)),[Produkt lev])<=5,[Produkt lev])

Can I do the same to get bottom 5?

[MK_QSL]

if(aggr(rank(-$(vActiveProductsR12)),[Produkt lev])<=5,[Produkt lev])

[jyothish8807]

Hi Bergendal,

Simply  put a - sign in the begging of your variable.

if(aggr(rank(-$(vActiveProductsR12)),[Produkt lev])<=5,[Produkt lev])

Regards

KC

[annabergendal]

Simple as that, thanks

[MK_QSL]

Just curious to know what was different in my answer 🙂

[annabergendal]

Nothing!

I just didn't know how I could tag both as correct answer

But thanks a lot!!

[MK_QSL]

Ha ha.... It's ok... You can only select one correct and maximum two helpful...

Nothing to worry !!

[annabergendal]

Actually, you could get an extra chance to help me, it seems it was not as simple as that... I miss some values...

I only get the bottom 4, and not in the correct order:"

"Produkt lev"  Value

1                    -2

2                    -2

3                    -10

4                    -31

I miss one with value -1, and the numbers should be in opposite order.

Also, if I make a selection, it seems it show the same as for top 5 chart.

Any ideas?

[jyothish8807]

Hi Anna,

Can you please share your app?

Try to sort values in assending order, it may work.

Regards

KC

[annabergendal]

Hi,

still having a problem with this, and can't share the app unfortunately...

My syntax for bottom 5 is now:

=if(aggr(rank(-($(vActiveProductsWeek))),[Produkt lev])<=5,[Produkt lev])

I sort with expression in ascending order.

The sorting is correct now with lowest value first, but I get less than 5 values, and not the values with zeros (which I want).

Is this a suppress zero problem?

Thanks