2 Replies Latest reply on Jul 23, 2018 9:54 AM by Hima vardhan Reddy Pavuluri

# Top 3 and Others with in sub-category ?

I am using sample superstore data to illustrate the challenge I am facing :

I need to divide sub-categories into top 3 with names and all others into Others based on sales with in each category with out using grouping (as it is static). Any ideas ?

Input :

Expected Output :

• ###### 1. Re: Top 3 and Others with in sub-category ?

Hi Hima,

1. sales zz = {FIXED year([Order Date]),[Category],[Product Sub-Category]:sum([Sales])}

2. rank sales : RANK(sum([sales zz ]))  convert it into discrete.

3. sub- category : if rank(sum([sales zz ]))<=3 then min([Product Sub-Category]) else "other" END

4. sales for other =if rank(sum([Sales])) > 3 then sum([Sales]) END

5.  grouped sales = if rank(sum([Sales]))<=3 then sum([sales zz ]) else WINDOW_SUM([sales for other])

END

6.  Show? = RANK(sum([sales zz ]))<=3+1

drag all the calculated fields as shown below:

Change "Default table calculation" for "rank sales","sub category","grouped sales" and "SHOW?" to

Drag "SHOW?" to filter and select true .

Hide header for "rank sales" and "product sub-category" and you will get the desired result as below:

• ###### 2. Re: Top 3 and Others with in sub-category ?

Thank you.