4 Replies Latest reply: Oct 11, 2010 12:50 PM by Henry Noh RSS

    RANK set analysis

    Henry Noh

      I have data with

      SALESPERSON
      SALESGROUP
      PRODUCT
      CLIENT
      REVENUE

      I'm trying to create a text box which when I select a SALESPERSON, I'm able to get the output:

      "Rank is 2 of 16"

      meaning Rank is 2 of 16 in this PRODUCT of all SALESPERSONs in that SALESGROUP.

      I know rank doesn't take set analysis, but I was wondering if this is at all possible? The way I have it now, whenever I click a salesperson, they become "Rank is 1 of 1".

       

      Thanks in advance!

        • RANK set analysis
          Neil Miller

          What expression are you currently using? I think you should be able to solve the problem by using the TOTAL modifier. Something like:

          'Rank is ' & Rank(PRODUCT) & ' of ' & Rank(TOTAL PRODUCT)


          You may need to modify the TOTAL modifier slightly depending on how that grand total should be calculated. For example if it is rank in that particular Sales Group, then:

          'Rank is ' & Rank(PRODUCT) & ' of ' & Rank(TOTAL <SALESGROUP> PRODUCT)


            • RANK set analysis
              Henry Noh

              I was trying to use an expression along the lines of:

              'Rank is ' & rank(Sum(REVENUE) & ' of ' & count(Total SALESPERSON) but this wasn't working as whenever I would select a SALESPERSON,

              the output would always be 'Rank is 1 of 1'.

              I tried using your expression but am getting a null value.

              I'm wondering if I should rank within the script to hardcode a rank value as an alternative option.

                • RANK set analysis
                  Neil Miller

                  Oh, I missed the part about the selection. You want to ignore the selection on SALESPERSON. Probably:

                  'Rank is ' & rank(Sum(REVENUE)) & ' of ' & count({<SALESPERSON=>} SALESPERSON)


                  That second part should get you the rank of the SALESPERSON ignoring any selections on that field. You may need the TOTAL too depending on how your dimensions are set up in the chart.

                  'Rank is ' & rank(Sum(REVENUE)) & ' of ' & count({<SALESPERSON=>} TOTAL SALESPERSON)


                  You may need to modify that Set Analysis if you are making selections on other fields that you don't want to affect the count. If you are selecting one SALESPERSON directly, that should work.