9 Replies Latest reply: Feb 3, 2014 3:26 AM by Nilendu Shekhar Ghosh

# Show total

I have a table like:

Brand    Product      Sale

J&J      Gillette      2

J&J      Baby Oil    3

Pepsi     Diet     1

Pepsi     Max     2

Coke     Diet     4

Coke     Classic     5

I just want to show the J&J product data and Total J&J data so, In Edit Script I'm using:

if(Brand = 'J&J', Product, Brand) as Brand

which gives me the result

Brand    Sales

Gillette       2

Baby Oil     3

Pepsi         3

Coke          9

Which is all good but how can I show Detail of J&J total? which in this case would be

Brand     Sales

J&J          5

Any help would be appreciated.

Thanks.

• ###### Re: Show total

Hi,

In your pivot chart you can use Brand as first dimension and if(Brand = 'J&J', Product, Brand) as a second dimension.

Can it fit what you want?

• ###### Re: Show total

I know I can achieve that with pivot table but is it another way through scripting? The example I am giving is really basic but I want to use the idea.

• ###### Re: Show total

If J&J has only those 2 products, it's possible to keep script as it is and make a calculated dimension in your chart:

If(Match(Brand,'Gillette','Baby Oil'), 'J&J', Brand)

If there's far more than 2 products in J&J, keep the info through a second field in script. It can be a simple flag, like

If(Brand='J&J', 1, 0) as [flag J&J]

There are, of course, many other possible ways.

• ###### Re: Show total

Thanks Manish for your response but I want to show all the other brand including the Product for J&J.

Brand    Sales

Gillette        2

Baby Oil     3

Pepsi         3

Coke          9

J&J            5

• ###### Re: Show total

Table1:

[

Brand, Product, Sale

J&J, Gillette, 2

J&J, Baby Oil, 3

Pepsi, Diet, 1

Pepsi, Max, 2

Coke, Diet, 4

Coke, Classic, 5

];

Join

Table2:

Brand,

Product,

SUM(Sale) as TotalSale

Resident Table1

Group By Brand, Product;

• ###### Re: Show total

Or This...

Table1:

[

Brand, Product, Sale

J&J, Gillette, 2

J&J, Baby Oil, 3

Pepsi, Diet, 1

Pepsi, Max, 2

Coke, Diet, 4

Coke, Classic, 5

];

NoConcatenate

Table2:

Brand,

Product,

SUM(Sale) as TotalSale

Resident Table1

Group By Brand, Product;

Drop Table Table1;

• ###### Re: Show total

if you are using Personal Edition... use below script....

Table1:

[

Brand, Product, Sale

J&J, Gillette, 2

J&J, Baby Oil, 3

Pepsi, Diet, 1

Pepsi, Max, 2

Coke, Diet, 4

Coke, Classic, 5

];

NoConcatenate

Table2:

Brand,

SUM(Sale) as TotalSale

Resident Table1

Where Brand = 'J&J'

Group By Brand;

Drop Table Table1;

• ###### Re: Show total

I suppose you want this output in table:-

Brand   Product   Sale

Coke    Diet      4

Coke    Classic   5

J&J     Gillette  2

J&J     Baby Oil  3

J&J     J&J       5

Pepsi   Diet      1

Pepsi   Max       2

Use the below script

[
Brand, Product, Sale
J&J, Gillette, 2
J&J, Baby Oil, 3
Pepsi, Diet, 1
Pepsi, Max, 2
Coke, Diet, 4
Coke, Classic, 5
];

NoConcatenate

Table2: