2 Replies Latest reply: Dec 3, 2013 6:05 PM by Erica Whalley RSS

    Scripting or Chart

    Manish Kachhia

      We have below purchase data in Brands vs Supplier format…

       

      Brand

      Supplier

      SUM(Purchase)

      B1

      A

      100

      B1

      B

      50

      B1

      C

      150

      B1

      D

      180

      B1

      E

      80

      B2

      B

      250

      B2

      C

      40

      B2

      D

      60

      B3

      C

      80

      B3

      D

      100

      B3

      E

      20

      B4

      C

      60

      B4

      D

      100

      B4

      E

      180

      B5

      A

      130

      B5

      C

      250

      B5

      D

      100

      B5

      E

      85

      B6

      A

      80

      B6

      B

      100

      B6

      D

      150

       

      For each Brand, we have particular main (prime) supplier and rest suppliers are we using only if that Brand is not available from our major supplier.

      So, I want below table…

      Here Others are total for non-prime supplier.

      How can I achieve this…

       

      Brand

      Supplier

      SUM(Purchase)

      B1

      A

      100

      B1

      C

      150

      B1

      E

      80

      B1

      Others

      230

      B2

      B

      250

      B2

      C

      40

      B2

      Others

      60

      B3

      C

      80

      B3

      Others

      120

      B4

      C

      60

      B4

      D

      100

      B4

      Others

      180

      B5

      A

      130

      B5

      D

      100

      B5

      Others

      335

      B6

      B

      100

      B6

      Others

      230

        • Re: Scripting or Chart

          Hi Manish

           

          You need to set up a table in the data that lists each Brand and their main supplier(s) (I am guessing from the above table that there could than 1?)

           

          ie so something like

           

          Brand

          Supplier

          Supplier_Type

          B1

          A

          MAIN

          B1

          C

          MAIN

          B1

          E

          MAIN

          B2

          B

          MAIN

          B2

          C

          MAIN

           

          Either join this to the main table or leave it floating, to be joined in the application.

           

          Then set up the table chart as you did earlier, except do a calculated dimension for supplier:

           

          Dimension1:Brand

          Dimension2(calculated): =if(Supplier_Type='MAIN',Supplier,'Others')

          Expression: =sum(purchase)

           

          This will group any suppliers that are not the main one for that brand.

           

          Erica

            • Re: Scripting or Chart

              NB Manish, as an aside it could be easier (depending on your data model) to construct a table with every permutation of Brand and Supplier, with the Supplier name or "others" as a 3rd field:

               

              Brand

              Supplier

              Supplier_Group

              B1

              A

              A

              B1

              B

              B

              B1

              C

              OTHERS

              B1

              D

              OTHERS

              B2

              A

              OTHERS

              B2

              B

              B

              B2

              C

              C

              B2

              D

              D

              B3

              A

              A

               

              This would be easier to apply in the chart, as you can add it straight in as a dimension. The downside is that it wont account for any new suppliers that are not yet in the list. So it depends on your data a lot really!

               

              Erica