3 Replies Latest reply on Feb 24, 2017 2:26 AM by Mark Fraser

    Dataset aggregation or group by

    giorgio donà
    giorgio donà Feb 24, 2017 1:25 AM

      Hi all,

      I need to change the aggregation view of my dataset. I make you an expample because the dataset I'm using is pretty big.

       

      Customer IDShopSales
      FrancoRoma1123
      FrancoRoma22344
      FrancoMilano566
      ClaudioMilano3
      ClaudioVenezia32

       

      I have a dataset like this one, with one record per shop and a lot of commercial variables.

      I need to re-manage my dataset and have one record per CustomerID and aggregate all the other numeric variables ( like sales).

       

      If it's not possible to manage the dataset how can I make a calculated field that, for example, sum all the sales grouping by customerID? Is it any formula for "Group by" ?

       

      Thanks for the attention

      Have a nice day

       

      Giorgio

      • 65 Views
      • Categories: Calculations, Formatting & Display
      • Tags:
        • 1. Re: Dataset aggregation or group by
          Mark Fraser
          Mark Fraser Ambassador Feb 24, 2017 1:54 AM (in response to giorgio donà)

          Hi Giorgio

           

          For start - i would alsways try and get the lowest level of dat you can and worry about the aggregation in tableau.

          tableau is set up for aggregating things liek this very easliy - as long as the data is set up and imported properly.

          in order to aggregate you need to keep only the columns you need - tableau will aggregate left to right automatically.

           

          you can do it via subtotal

           

          or remove shop

          and you can just add more items

           

          hope that helps!

           

          Cheers

          Mark

          • 2. Re: Dataset aggregation or group by
            giorgio donà
            giorgio donà Feb 24, 2017 2:22 AM (in response to Mark Fraser)

            Hi Mark, thanks for your attention!

            I show you my case so maybe you can help me with the real problem I'm facing.

            In my database the max detail is the POD ( electric meter-box ) and for each one i have an energetic consuption, but the analysis detail i want to have is by ID_company. ( An ID_company can have different PODs with different energetic consuptions).

            So, the problem is this one: I grouped the energetic consuptions in 9 classes ( <2, 2-25, etc.) and now I want to count dinstinct how many ID-company i have for each class.

            I setted the view in percent of the total, but the total, as you can see, is not 100% ( but 153%) and i think it's becuase  a company can have 2 PODs and they can belong to differents classes so they appear twice in my bar charts.

             

             

            I just want to aggregate the PODs and have my view having the ID_company as the maximum detail

            Hope you understood what i'm meaning.

            Thanks for your help

             

            Giorgio

            • 3. Re: Dataset aggregation or group by
              Mark Fraser
              Mark Fraser Ambassador Feb 24, 2017 2:26 AM (in response to giorgio donà)

              Hi Giorgio

               

              My first reaction is partioning - you need to define the calcluation to restart/ partition based on POD

              either partioning and/or LOD functions, you force the aggreation seperately for PODS within the same Id_company

               

              will try and find something as a basic exmaple

               

              Cheers

              Mark