6 Replies Latest reply: Feb 8, 2012 8:56 AM by Amir Vastani RSS

    Rank function problem

    Amir Vastani

      I have a straight table with the dimension Salesman



      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?



        • Rank function problem
          Stefan Wühl

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


          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?





          • Rank function problem

            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
                Amir Vastani

                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

                    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.