6 Replies Latest reply on Jan 23, 2018 7:32 AM by Alex Neumann

    top n by date

    Ronak Patel

      I have a date file on the columns and customer names on the row. How do I do a top N by each date? Right now the default top 10 looks at the whole population but I want it to do it by date.

        • 1. Re: top n by date
          Ronak Patel

          I see there there is a window_sum function but how would I write the top 10 part of it?

          • 2. Re: top n by date
            Ronak Patel

            still attempting a solution for this but still haven't really gotten one to work...anyone give this a shot?

            • 3. Re: top n by date
              Derk Busser

              Hi Ronak,

               

              What i would do is create a set of the dates and customers, then you can rank based on the metric that you would like, for instance the profit or visiting times etc. Wat you then need to do is to create a second metric that can help you in creating a rank from 1 to n, e.g. 1,2,3,4,5,etc. This is possible by using a window_sum(sum(number of records)/sum(number of records)) table calculation computed based on the SET so that it restarts counting every new date. When this is all set, just filter all values of your table calculation above your N (10) and you should be all set.

               

              I have included my sample made with the coffee set.

               

              From what i have seen, it will be a lot easier in V8, but for now this should do.

               

              I hope this is what you needed.

               

              -Derk

              • 4. Re: top n by date
                Landon Conover

                Hey,

                 

                I know this post is very old by now, but it was one of the top results in google. I thought it might be helpful for others with this problem.

                 

                All I had to do is right click on the date filter and select "Add to Context" and that got it to filter the top n by date.

                4 of 4 people found this helpful
                • 5. Re: top n by date
                  Louisa Lei

                  This is super helpful! Thank you.

                  • 6. Re: top n by date
                    Alex Neumann

                    I have been searching for a solutions to an issue with my top N filter for several days.  When I realized that it was not filtering correctly because it was not taking into account the date ranges, it brought me to your post.  THANK YOU!!! This was super helpful.