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

    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

      in addition to above.

       

      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

              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.

                • 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
                Manish Kachhia

                Please check enclosed file.

                  • 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
                        Manish Kachhia

                        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;

                          • Re: Show total
                            Manish Kachhia

                            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;

                      • Re: Show total
                        Manish Kachhia

                        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;

                        • Re: Show total
                          Nilendu Shekhar Ghosh

                          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.