Line chart should show top 4 brands + specific brands

[#1]

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,

Load *Inline
[
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.

Thanks in advance.

[#2]

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

[sunny_talwar]

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:

Load * Inline [

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: