2 Replies Latest reply on Oct 26, 2015 8:40 AM by Joe Oppelt

    Can we show the Top 5 and Bottom 5 sales in the same sheet

    Sridhar P

      How to display top 5 and bottom 5 in the same view

        • 1. Re: Can we show the Top 5 and Bottom 5 sales in the same sheet
          Mark Bradbourne

          Create two Sets, One for Top 5 and One for Bottom 5. Then combine the sets showing all members form both sets.

           

          Everyone Love Hot Dirt Sets.

          • 2. Re: Can we show the Top 5 and Bottom 5 sales in the same sheet
            Joe Oppelt

            The RANK() function will let you see all the values in order.  (You may have to play with TABLE(down) and TABLE(across) to make RANK work correctly.)

             

            The INDEX() function will let you know how many values you have.

             

            You can compare RANK and INDEX to get top-5 and bottom-5.  To get bottom-5, for example, you would do something that looks like this:

             

            if RANK([Total Sales]) >= window_max(Index()) - 4  then [Total Sales] end

             

            That woud load the values of the bottom 5 values in the calc, and load null in all the rest.  Display the calc, and you'd get the bottom-5.

             

            (  You can also specify 'ASC' or 'DESC' in the RANK function and I think that would allow you to skip messing with INDEX().  But you can play with that to make sure.)

             

            Do the same sort of thing for top 5.

             

            You might be able to do both in one calc (just "OR" the two together) and that would load top-5 and bottom-5 into one calc instead of two, but I'm not sure what all the intervening NULLs will look like.  You'll have to play with that to see.