# Top N in 2018

hi

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

Hi Ben,

Try this calculation

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

Thanks

sankar

• ###### 2. Re: Top N in 2018

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

hi Ben,

No need to create any set.

do this

drag orderdate into detail

Directly create this and add into filter and select true.

Thanks

sankar

• ###### 4. Re: Top N in 2018

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

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'

END

• ###### 6. Re: Top N in 2018

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?

Thanks