11 Replies Latest reply: Jun 20, 2016 6:50 PM by Fei Xu RSS

    Can "Rank" function achieve this?

    Fei Xu

      Please see attached sample application.

      Thanks.

        • Re: Can "Rank" function achieve this?
          Stefan Wühl

          I am not exactely sure how you want to calculate your rank, thus I am attaching a sample with two different rank calculations,

          one calculating total sales per department and group and ranking the department sales in the group dimension, one calculating total sales per department and group and ranking the sales for the selected group in the department dimension.

           

          You should be able to adapt accordingly.

            • Re: Can "Rank" function achieve this?
              Fei Xu

              Hi Swuehl,

              Thank you very much for your reply. Table "CH01" is what I need to sort out.

              In Group A there are only two stores (Store A and Store B). If we select Store A, all the three departments D1, D2 and D3 rank 2 in the group, which is correct by your calculation. However when we select Store B, all the three departments should rank 1 in the group but your calculation is still showing 2, which is not right.

               

              Thanks anyway.

               

              Fei

                • Re: Can "Rank" function achieve this?
                  Stefan Wühl

                  Department3 shows rank 2 in both cases because there is a tie in ranking, Sum(sales) is 900 for both groups.

                   

                  You can change the tie breaking a bit using the additional arguments to rank() function.

                   

                  Why do you show 2 for Group A and 1 for Group B, if both groups share same sales amount?

                    • Re: Can "Rank" function achieve this?
                      Fei Xu

                      Sorry I didn't explain clearly what I want.

                       

                      This is a table part of a benchmark group analysis application. In this application, stores compare their sales with benchmark group average and find out the rankings for department items within the group.

                       

                      What I want to show is the department rank for a store within this store's group. The group here is the benchmark group for the store. So stores are selling all sorts of department items, what are the rankings for these departments within the group?

                       

                      Users need to select one store to view the reports. CH01 is to show the item department rankings for the selected store within the group. Therefore all the rankings for the three departments should be 2 for Store A and 1 for Store B.

                       

                      Thanks.

                       

                      Fei