6 Replies Latest reply on Jul 18, 2018 3:07 AM by Ben Broadhead

    Top N in 2018

    Ben Broadhead


      I want to create a table which presents top N products for 2018 but shows the revenue they generated for 2017. Is there a formula to create a set for top N in a year?




      Many thanks

        • 1. Re: Top N in 2018
          Sankarmagesh Rajan

          Hi Ben,


          Try this calculation

          if attr(year([Order Date]))=year(today()) and INDEX()<=10 then 'Top' else 'others' end




          • 2. Re: Top N in 2018
            Ben Broadhead

            Hi thanks for this


            What is ATTR? Can't seem to get it to recognise it in formula.


            Also do you mean use this formula in edit set?

            • 3. Re: Top N in 2018
              Sankarmagesh Rajan

              hi Ben,


              No need to create any set.

              do this

              drag orderdate into detail

              Directly create this and add into filter and select true.


              Attr added for aggregation purpose.




              • 4. Re: Top N in 2018
                Naveen B

                Hi Ben,


                I have taken example superstore data set use the below calculation to achieve your result


                1) Create a calculated field to extract 2018 sales


                Note : {max(year(orde date))} will max year in the dataset my dataset have data for the year 2018 as Max



                2) create a calculated field to extract 2017 sales


                3) Drag Product Name to Rows and drag 2018 sales to detail

                4) Drag Product Name to filter and get the top 10 on 2018 Sales value

                I have given 10 you can create parameter to get top N

                5) Drag 2017 Sales to text

                6) Change the tooltips accordingly


                Hope this helps kindly mark this answer as helpful or correct so that it will help others

                • 5. Re: Top N in 2018
                  Ben Broadhead

                  Hello again

                  I have tried this forumla but it comes up with errors. I'm using planned departure date as my order date




                  attr(year([Planned Departure Date]))=year(today())



                  AND INDEX()<=20 THEN 'Top' else 'others'




                  • 6. Re: Top N in 2018
                    Ben Broadhead

                    Hi guys


                    Just to clarify here


                    I would like to see top 20 products for 2018 but also show how they did in 2017

                    I would also like to show the rest of products as a total so it shows total performance 2018 vs. 2017 also.


                    from playing around too I have noticed that additional filters does not keep the top 20. So for example if i want to look at top 20 regions it doesn't seem to dynamically work and will only show top 20 overall for all regions. Does that make sense?