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

# Scripting or Chart

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

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