4 Replies Latest reply on Mar 6, 2016 5:50 PM by Fei Sun

# How to concatenate top N sales subcategory into one string for better display?

Hi,

I am working on this problem, display top 5 sales subcategory per customer.

I have one achievable way is to use the combined field and found top 5 sales using filter. But the issue is with the display, we need to keep the height of each of the segments (in this case customer name) the same height to maintain the format of the dashboard.

I thought we could concatenate the top 5 best selling sub-categories for each customer name. In other words, I would like the visual to be similar with the visual effect described in the below-link.

Appreciate any feedback or guidance!

• ###### 1. Re: How to concatenate top N sales subcategory into one string for better display?

Same approach applies as you had shared in the link.

The table calculation will be across different dimensions.

We need SubCategory & Customer field sorted descending on SUM([Sales]) to give us Top-5 Subcategories for each customer.

We can then use FIRST()==-4 to select the Top-5 Sub Categories and their concatenated values.

Finally for sales, you can do a Running_Total calculation on Sales to get the total sales value for these top-5 products.

2 of 2 people found this helpful
• ###### 2. Re: How to concatenate top N sales subcategory into one string for better display?

Hi Mosin,

There is an problem using the FIRST()=-4 filter. it will filter out custom with less than 5 sub-categories. which means, if the customer only have 4 categories. it will not be shown.

Please take a look at attached workbook.

I used FIRST() = -[TOPN] or LAST = 0 as the filter to demonstrate the problem.

Is there a way to show FIRST() = -[TOPN]  if number of subcategories is more then N, else show LAST()=0 ?

I am stacked at the part how to calculate number of subcategories per customer.

• ###### 3. Re: How to concatenate top N sales subcategory into one string for better display?

Others may have a better solution. I would just update the filter and instead use:

```IF SIZE()>=4 then FIRST()=-4 else FIRST()=-(SIZE()-1) end
```

where the value TRUE. This is what I get. (Highlighted record with less than 5 categories)

3 of 3 people found this helpful
• ###### 4. Re: How to concatenate top N sales subcategory into one string for better display?

Hi Mosin,

Thank you. Not quite familiar with the tableau build in function yet.

Just a small correction of the filter , it should be using > instead of >=

IF SIZE()>4 then FIRST()=-4 else FIRST()=-(SIZE()-1) end