Rank function problem

[avastani]

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.

[swuehl]

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

[avastani]

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

[#4]

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

[avastani]

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.

[#6]

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.

[avastani]

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