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

    Line chart should show top 4 brands + specific brands

    Laxmaiah Chowdary

      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.

       

       

       

       

       

       

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

          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
            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:

            Capture.PNG