2 Replies Latest reply: Jul 23, 2016 12:25 PM by Sunny Talwar

# Line chart should show top 4 brands + specific brands

Hi Folks,

I come across a scenario where sales of top 4 products to be shown on Line chart along with specific brand products irrespective of their sales performance.

For example,

[
Brand, Product, Sales
Merck,  A,  100
Merck, B, 120
GSK, C , 80
Pfizer, D, 130
Novartis, E, 150
Sanofi, F, 110

]

Dimensions: Year, Product

Here I want to show sales of top 4 products but always show Merck products A and B. Thus Line chart should show A,B, D, and E though A does not fall under top 4 brands.

I have tried with Rank function but I could only restrict top 4 brands and A is not included.

Could you please suggest a way to do this.

• ###### Re: Line chart should show top 4 brands + specific brands

Hi Lexmaiah!

In the Line Chart Properties, proced as following:

1) In Sort tab, promote the dimension PRODUCT to the first priority of sort. Then, with the PRODUCT column highlighted, check only the Sort by Expression. Choose Descending and input the expression: = if(Product='A',1000, sum(Sales)). This will put the product A as the first sorted product (as 1000 is much more than the highest product sales).

2) In Dimension Limits tab, check the "Restrict witch values..." option. Then choose "Show Only", choose "FIRST" and type 4 on values. In Options (at the tab botton) uncheck "Show Others".

With your In Line example this worked! Hope it helps you!

Best regards,

Marcello

• ###### Re: Line chart should show top 4 brands + specific brands

May be like this?

=Sum({<Product = {"=Rank(Sum({<Brand -= {'Merck'}>} Sales), 1, 1) < (5 - Count(TOTAL DISTINCT {<Brand = {'Merck'}>}Product))"} + P({<Brand = {'Merck'}>}) >}Sales)

Changed your sample data to add Year field and doubled the number of observations to see two years:

Table:

Year, Brand, Product, Sales

2016, Merck,  A,  100

2016, Merck, B, 120

2016, GSK, C , 80

2016, Pfizer, D, 130

2016, Novartis, E, 150

2016, Sanofi, F, 110

2015, Merck,  A,  90

2015, Merck, B, 140

2015, GSK, C , 60

2015, Pfizer, D, 135

2015, Novartis, E, 132

2015, Sanofi, F, 120

];

This is what I see in the line chart: