topic use a filter in a straighttabel in QlikView

use a filter in a straighttabel

Thu, 22 Jul 2010 15:04:38 GMT

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

use a filter in a straighttabel

Thu, 22 Jul 2010 16:07:25 GMT

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

use a filter in a straighttabel

Thu, 22 Jul 2010 16:27:56 GMT

Hello JJJ,

Is it possible to include this in the same dimension?

Regards,

Aissam

use a filter in a straighttabel

Thu, 22 Jul 2010 17:21:09 GMT

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

use a filter in a straighttabel

Fri, 23 Jul 2010 08:02:54 GMT

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

use a filter in a straighttabel

Fri, 23 Jul 2010 14:25:39 GMT

Hi,

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

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

Good luck

JJJ

use a filter in a straighttabel

Fri, 23 Jul 2010 14:33:24 GMT

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