7 Replies Latest reply on Oct 30, 2018 11:11 AM by rosie chi

# Sorting sets created with Top N

Hi there,

I have a set that is created with Top N condition sorted by the sum of the field with data filtered to 2017 only (this is the top 15 products in desc order of sum of sales).  I would like to use this set to create a trend from 2016 to 2017, but I also want both tables to match up so that they can be shown side by side (sort order of the products by total sales in 2017 is the same order as the table that shoes its trend).  So in effect, I would have the following columns:

| Product | Total 2017 Sales | Sales Trend from 2016 |

How can I keep the sort order the same for the trend as for the Top N?

• ###### 1. Re: Sorting sets created with Top N

Could you please attach a twbx workbook

• ###### 2. Re: Sorting sets created with Top N

Here is a visual of what I am trying to do...

and the workbook

• ###### 3. Re: Sorting sets created with Top N

Please note that the Sum Sales is created with data for 2017 only.  Perhaps there is a better way to do this?  Perhaps on a single worksheet (but I did not find a way to apply different filters to different columns within 1 worksheet).  Any help would be highly appreciated!

• ###### 4. Re: Sorting sets created with Top N

Like this  ? -

I have gone for calculated fields to maintain consistency. You can show/hide the columns depending on the requirements.

Cheers,

Om

1 of 1 people found this helpful
• ###### 5. Re: Sorting sets created with Top N

Going for calculations with IF/ELSE would be helpful in this case.

1 of 1 people found this helpful
• ###### 6. Re: Sorting sets created with Top N

Om,

That's a very interesting solution!  Let me see if that will work with my actual dataset tomorrow, but as far as I can tell, it meets all my requirements...its even within a single worksheet!  Hey, thanks a lot for your help!

• ###### 7. Re: Sorting sets created with Top N

Marking this question as answered!  The solution worked...just note to include all filter combos in the IF/Case statement

ex:

if year([Order Date]) = 2016 and category = 'furniture'

then [Sales]

END

...

since I was initially getting weird results with my sorting, anyways...thanks so much Om!