7 Replies Latest reply on Dec 8, 2018 2:40 PM by Rob Adams

    How to do sub select type data aggregation in Tableau

    Rob Adams

      I can write fancy sql (Db2) and generate my results. Am I better off doing this as a view, custom sql as a data source or is there an out of the box way to do it in Tableau with something like a left outer join.

       

      Data model: Bid_header (parent) , bid details (orig_pod,dest_pod,eqp_type (van, flat,reefer), customer_volume). This is a list of all bids we did for customers and the individual lanes (orig_pod,dest_pod). I want to over lay actual shipment data for these same lanes and eqp_type. The table is called BOSS_BI_SHIPMENTS. (orig_pod,dest_pod,eqp_type, margin). If I add this table in the data source as a left out join then my sum of measures (customer volume) are affected. If a lane has 100 for customer volume , but we moved ten loads in that lane I get 1000, which is not what I want. I want to show MN-MIN , IL-CHI, 100, 10, $4,000. The last two fields would come from shipments table. In SQL this is easy, I just define the number of loads and margin as subselect queries.

       

      Ultimately I want to add a third table in here counting the # of phone calls operations made to find the trucks to move the shipment.

       

      What should be my approach in Tableau (10.4)?

       

      Thanks in advance!

       

      Rob.

        • 1. Re: How to do sub select type data aggregation in Tableau
          Norbert Maijoor

          Hi Rob,

           

          Could you share your workbook in .tbwx format?

           

          Regards,

          Norbert

          • 2. Re: How to do sub select type data aggregation in Tableau
            Jim Dehner

            Good morning Rob

            Agree with Norbert I would prefer to see the twbx workbook you are using

            but the result you are describing sounds like your 2 (soon to be 3) files are at different levels of detail (eg one is record level data the other has been aggregated to header level data) - your header level data is repeated across each of the record level data and get multiplied during the aggregation process - so what to do

            there are several approaches the could work - first in Tableau proper recognise when you are using header level data and use a Min(), Max(), or AVG() aggregation function vs sum() - a different approach would be to bring the files into Tableau Prep and use the Aggregation function there to bring your record level data to the same level as your header level and then join the files together - you could then output the file to Tableau Proper

             

            Jim

            If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

            • 3. Re: How to do sub select type data aggregation in Tableau
              Rob Adams

              Thanks guys. I have attached the two tbwx files. Ultimately, I am looking for a filled map with custom territories based on groups of 5 digit zips similar to this. (zip.tbwx file). This is really the definition of our territories. About 126 pods made with groupings of 5 digit zips. I created a separate workbook to play just with the map you see below. Except I want a heat map where the color of the territory is based on the number total of customer volume (from bid_line).

               

              I am then looking to attached summary information on the shipments associated by orig-pod, dest_pod and eqp_type (defined in the data source left outer join.power lanes test.tbwx). You will see in power lanes test.tbwx my numbers are crazy high because of the left outer join. My highest numbers should be a couple hundred thousand, not 10M + Sounds like this is resolved with a different aggregation function as Jim suggests. I did play with a fixed function, but that either wasn't the right choice or I did it wrong. I will explore Jim's path on the aggregation function.

               

              • 4. Re: How to do sub select type data aggregation in Tableau
                Jim Dehner

                the files you sent do not have the data you need to extract the data and send as a twbx

                Jim

                • 5. Re: How to do sub select type data aggregation in Tableau
                  Rob Adams

                  Yeah, that would be helpful. I updated the files on my reply.

                   

                  Thanks Jim.