use a filter in a straighttabel

[#1]

Dear QV fellows,

I have made a straighttabel, I have one dimension and one expression. I would like to filter the expression field to just to see the 10 biggest values and the 10 lowest values. Can someone help me with this. Attached you will find an excel file with an example.

With kind regards,

Aissam

[#2]

Hi

I upload an example with your data.

The main idea is to create a new dimension as

for the top 10

=if( aggr( rank( sum([Internal Billing])) , JOB_NUMBER) <=10 , JOB_NUMBER , Null())

& for the flop

=if( aggr( rank( -sum([Internal Billing])) , JOB_NUMBER) <=10 , JOB_NUMBER , Null())

This works if your expression is positive.

JJJ

[#3]

Hello JJJ,

Is it possible to include this in the same dimension?

Regards,

Aissam

[#4]

Of course :

Just replace the calculated dimension by :

=if( aggr( rank( sum([Internal Billing])) , JOB_NUMBER) <=10 or
aggr( rank( -sum([Internal Billing])) , JOB_NUMBER) <=10
, JOB_NUMBER , Null())

JJJ

[#5]

Hi JJJ,

Thanks for your reaction, but I only receive the biggest 10 and not the lowest 10. Here is my formula I used:

=if(aggr(rank(sum(JC.NetBilling)),JOB_NUMBER) <=10 or aggr(rank(-sum(JC.NetBilling)),JOB_NUMBER) <=10 , JOB_NUMBER, Null())

Regards,

Aissam

[#6]

Hi,

It seems to work with my attachement. Have a look.

Maybe it's an option to check or to uncheck.

Good luck

JJJ

[#7]

Hi JJJ,

It works, but it shows only if the value is negative. Is there a way to see the top 10 bad order when the values are positive.

Regards,

Aissam