3 Replies Latest reply on Mar 13, 2016 6:45 PM by Siva Gabbi

    Aggregation problem

    Siva Gabbi

      I have gone through all the forums and this seems to be a loophole in tableau than anything else.. I just want to confirm before I go solving the problem in the database.

       

      I have the following situation: Typical ecommerce situation: you have orders, assets ( assets change hands as they are rented), customer asset allocation, order items (if 2 items in an order we have 2 order lines) , payments (aggregated on an order level)

       

      I tried joining, blending, many combinations but I am not able to get a clear picture of the # of payments made for an asset. attaching a workbook..

       

      Table:

       

         orders:

      order idcustomerorder create timeorder price

       

      customers:

      idcreate time

       

      asset:

      idcustomer idpurchase pricepurchase time

       

      customer asset allocation:

       

      caaidassetidcustomeridorder idOrder line id

      Allocated Date

       

      order items:

      order idorder line idorder datepurchase price

       

      payments:

      payment request idorder idcustomer idPayment DatePayment Amount
        • 1. Re: Aggregation problem
          Siva Gabbi

          Any one to help me?

          • 2. Re: Aggregation problem
            Adam Crahen

            Hi Siva-

             

            I took a look at this.  I think the main problem you are having are the multiple one to many relationships across your tables. Shawn highlighted this above.

             

            The blended sample you have is going to be difficult to work with because you need to have a relationship from each table to the primary source.  On your sheet 3, your primary is assets, which has a relationship to customer asset allocation table, but as you see the order table does not have a relationship with assets which is why you are getting * there.

             

            So I tried to join it.  This still isn't going to be much better because of the one to many.

            order id: oid-4 has a total of $63 payment amount.  But we have those line ids you want to see so $63*9 = 567

            You can try some hackery with some calcs to get to the order totals you expect.

             

            For example, here if there is more than 1 order line id, I took an average, if not the sum.  Order 4 now totals $63.

             

            Then I tried to put your tables in access to see if it is any better

             

            Looks familiar right?

            So I think you are going to either need to restructure your data or use multiple worksheets to tell your story.  As you can see, Tableau reproduced the same joined result as access.

             

            I am attaching everything here.

            • 3. Re: Aggregation problem
              Siva Gabbi

              Hi Adam,

               

              Really really appreciate it.. and sorry for the late reply i was traveling..  I came to the same conclusion as you before i posted this.. my only question was if there is a work around this or If I have to write a sql query in the backend and use only to visualize..