Show total

[#1]

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

in addition to above.

Any help would be appreciated.

Thanks.

[#2]

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?

[#3]

Thanks for your response.

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.

[MK_QSL]

Please check enclosed file.

[MK_QSL]

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

Table1:

Load * Inline

[

  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:

Load

  Brand,

  SUM(Sale) as TotalSale

Resident Table1

Where Brand = 'J&J'

Group By Brand;

Drop Table Table1;

[#6]

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

[#7]

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.

[MK_QSL]

Table1:

Load * Inline

[

  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:

Load

  Brand,

  Product,

  SUM(Sale) as TotalSale

Resident Table1

Group By Brand, Product;

[MK_QSL]

Or This...

Table1:

Load * Inline

[

  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:

Load

  Brand,

  Product,

  SUM(Sale) as TotalSale

Resident Table1

Group By Brand, Product;

Drop Table Table1;

[Anonymous]

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

Load * Inline
[
  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:
Load
  Brand, Brand as Product,
  SUM(Sale) as Sale
Resident Table1
Where Brand = 'J&J'
Group By Brand;

Hope this meets ur requirements.