3 Replies Latest reply on Apr 11, 2013 10:44 AM by Philip George

    aggregate functions

    Philip George
    Philip George Sep 17, 2012 11:51 AM

      Hi,

       

      I have data like this

       

      region  sales

      A           3

      B          4

      C          8

      A          7

      B          2

      A          4

       

       

      can I create a calculated field which store region wise total sales. something like sum(sales) group by region.

       

      one solution that I found is creating an aggregate table which store region and sum(sales) group by region (using custom sql)

       

      is there any other way to get data like this.is there any solution like  aggr(sum(sales),region)

       

      philip

      • 698 Views
      • Categories: Calculations, Queries & Performance
      • Tags:
        • 1. Re: aggregate functions
          Mark Holtz
          Mark Holtz Sep 17, 2012 12:41 PM (in response to Philip George)

          Sounds like you could create a calculated field with WINDOW_SUM(SUM([Sales])).

          Then you would just need to make sure you include the [Region] dimension in your view in the Rows or Columns shelf.

           

          The calculated field will generate a Table Calculation, for which you can control the field(s) used to group the "windows."

           

          Good luck

          • 2. Re: aggregate functions
            Anton Zhukov
            Anton Zhukov Apr 11, 2013 7:46 AM (in response to Philip George)

            Hello, Philip.

             

            I've just faced the same problem - I need to aggregate some measures across the dimension that is not used in the view.

            When I used QlikView earlier there was a powerfull function AGGR(avg(measure), dimention1, dimention2..), but I couldn't find anything simmilar in the Tableau.

             

            Did you solve this problem already?

            • 3. Re: aggregate functions
              Philip George
              Philip George Apr 11, 2013 10:44 AM (in response to Anton Zhukov)

              Hi Anton,

               

              Did you try custom sql?

               

              Actually I have tried the function you mentioned for Qlikview.But in Tableau we don't have any function similar to that.But Tableau provide a feature called table calculations using that you can aggregate measures used in view.But for that dimensions should be there in the view.

               

              Regards,

              Philip