topic Rank function problem in QlikView

Rank function problem

avastani — Wed, 07 Dec 2011 15:57:16 GMT

I have a straight table with the dimension Salesman

Expressions

1. Actual = sum({<salestype={'DIRECT','INDIRECT'}, $(CurrentPeriod)>}LineAmountUSD)

2. Budget = SUM({<$(CurrentPeriod)>}BudgetAmountRep)

3. AvB % = if(Actual = 0 or Budget = 0, null(), Actual / Budget)

4. No. = if([AvB %]=0 or Len([AvB %]) = 0,0,rank([AvB %]))

I also have Suppress when value is null in Dimension, Suppress Missing and Suppress Zero Values checked.

However, I still have inactive employees showing with Rank = 0, although I specifically have suppress zero values checked. If I disable this expression, the inactive employees go away, but seems like the Rank() function is bringing them back in. Anyway to stop that possible?

Thanks.

Rank function problem

swuehl — Wed, 07 Dec 2011 17:43:14 GMT

Yes, the rank() is bringing them back in, there is a note to the Help topic for rank function:

Note:
Suppression of zero values is automatically disabled when these functions are used. NULL values are disregarded.

Hm, I remember vaguely that I fought with this some time ago, I just don't get if and how I handled this.

Could you upload a small sample file? And on which version are you?

Regards,

Stefan

Rank function problem

avastani — Wed, 07 Dec 2011 17:54:56 GMT

this is v9. I will try to upload a sample.

Rank function problem

Tue, 07 Feb 2012 15:51:22 GMT

Hi amirvas,

I'm facing the same problem as you. An expression witj the rank funktion brings null values back into my table.
Did you ever find a solution?

Regards, Jens

Rank function problem

avastani — Tue, 07 Feb 2012 16:40:00 GMT

try the expression

if([AvB %]=0,0,rank([AvB %])) and then force the sort on numerical order on this expression.

Also check in the Dimension, Suppress when value is null and in the Presentation, Suppress Missing and Suppress Zero Values checked.

This will default the bad results to rank 0. hope this helps.

Rank function problem

Wed, 08 Feb 2012 09:08:06 GMT

The proposed method will only give the bad results a rank of 0 - not hide them.

The table consists 3 expressions which use set analysis to hide unwanted rows, whick are revealed when the rank og rowno functions are used.

Basically I just want to have a line number in the table, so the user easyier can keep track of progress.

Rank function problem

avastani — Wed, 08 Feb 2012 13:56:25 GMT

Ideally the Suppress 0 should hide them, but if there is any expression that is non-zero then you will see that Dimension value's Rank = 0