3 Replies Latest reply on Jul 5, 2018 12:21 PM by Ken Flerlage

    Top Records are not filtered Properly

    venkatram

      Hey Folks,

       

      Hope everyone busy with work schedule, I have an issue while I used Top records in the worksheet.

       

      Problem:

       

      Filter Top 5 records, and output like below

       

      Expected output: From excel file I have data like below.

       

      Deepak Rai

      Shinichiro Murakami

      kettan

      Joe Oppelt

       

      Attached Sample Files for better understanding. 

        • 1. Re: Top Records are not filtered Properly
          Ken Flerlage

          Top X filters are applied to the entire data set--it does not account for the dimensions placed on your view. So, in your case, it's first finding the top 5 projects overall, based on total hours and filtering down to only those 5--d, m, h, k, n. You'll need a different approach if you want to get the top 5 to account for the dimensions on your view. I'd recommend using the RANK functions (Table Calculation Functions). RANK_UNIQUE will give you a unique number (it won't give the same number for ties) so that might be good to use here (but, if you have ties on say the fifth item, it will make one of those 5 and one 6, so if you're only showing top 5, that 6th one will be filtered. If you don't want that then use the RANK_DENSE function). I'd create a calculated field called Rank with the following definition:

           

          Rank

          RANK_UNIQUE(SUM([Hours]))

           

          Then create another calculated field as follows:

           

          Top X Filter

          IF [Rank]<=[Top 10 Project Hours] THEN

              "Include"

          ELSE

              "Exclude"

          END

           

          Add Top X Filters as a filter, then right-click on the pill at edit the table calculation to look like this:

           

           

          This will account for the dimensions in your view.

           

          See attached workbook. If this solves your problem, please mark this answer as correct. This will help others with similar questions in the future. Thanks!

          • 2. Re: Top Records are not filtered Properly
            venkatram

            Thank you, Ken.

             

            Great Appreciate for your help. It's working.

            • 3. Re: Top Records are not filtered Properly
              Ken Flerlage

              Great. Happy to hear it!!