2 Replies Latest reply on Apr 23, 2013 1:54 PM by Stefano Grioni

    Aggregate amounts using multiple criteria and then comparing them to eachother

    Stefano Grioni

      Hello,

       

      First of all, I must point out that although I have quite some experience in SQL and some BI tools, I am fairly new to Tableau; apologizes if my question spans from a wrong understanding of how Tableau handles aggregation.

       

      My data set is normalized so that each entry, corresponds to one particular financial element of one particular invoice:

      Capture.PNG

      This is very helpful to us since in the table we also track discounts, which can vary between markets, geographies,...

       

      My challenge is that I would like to do comparative analyses on the amounts sales/profit, aggregated by InvoiceID (and then from there, by Invoice type etc.).

       

      If I would translate my request into SQL, it would result in:

       

      SELECT a.[Invoice ID], a.P/b.S FROM

      (SELECT  [Invoice ID], SUM(Amount) as P FROM my_table WHERE [Discount type] = 'Profit' GROUP BY [Invoice ID]) a

      JOIN

      (SELECT  [Invoice ID], SUM(Amount) as S FROM my_table WHERE [Discount type] = 'Sales' GROUP BY [Invoice ID]) b

      ON a. [Invoice ID] = b. [Invoice ID]

       

      Do you understand what I am trying to achieve?

       

      Thank you very much,


      Sincerely,

       

      Stefano

        • 1. Re: Aggregate amounts using multiple criteria and then comparing them to eachother
          Tracy Rodgers

          Hi Stefano,

           

          I'm not the greatest with SQL, Alex Kerin may have some insight on this--I think this logic can be used in Tableau's SQL.

           

          From a the standpoint of not using SQL in Tableau, I would probably reshape the data so that Sales and Profit were each in their own columns. Then, a calculated field could be created similar to the following and place Invoice ID on the rows/columns shelf:

           

          sum(Sales)/sum(Profit)

           

          The following KBs gives more details about how to set up an Excel sheet and using the Re-shaper Tool:

          http://kb.tableausoftware.com/articles/knowledgebase/preparing-excel-files-analysis

           

          http://kb.tableausoftware.com/articles/knowledgebase/addin-reshaping-data-excel

           

          Hope this helps!

           

          -Tracy

          • 2. Re: Aggregate amounts using multiple criteria and then comparing them to eachother
            Stefano Grioni

            Thanks Tracy,

             

            I understand your point. Unfortunately I doubt that passing by Excel would be an option since we have a quite significant amount of data to treat. This being said, that operation can easily be performed in SQL, and with a lower cost.

             

            I guess that my question comes back to how does Tableau deal with aggregation. I was asking a more experienced colleague earlier about how to address multiple-aggregation challenges (see below), and all his answers boiled down to actually have some other tool (being Excel, SQL or else) do that part of the job, and then let tableau take care of the visualization. If this is correct, and please feel free to jump in if not, I must admit that I am a bit disappointed, since this would be a piece of cake in several competing BI tools.

             

            Thank you,

             

            Stefano

             

            Would you see how to deal with this without any pre-treatment in SQL:

            I have in my dataset geographic regions, each one connected to several customers (e.g. 100 customers in London), each one having several Invoices (e.g. ABC Ltd. has bought 10 products). I want to be able to differentiate:

            • the average selling price in a region: SUM(All invoices in that region)/COUNT(All invoices in that region)
            • the average selling price, to customers, in a region: AVG(SUM(Invoices of each customer, separately)/ COUNT(Invoices of each customer, separately))

            Obviously the two will give a very different example since the first one only factors the volume of sales, whereas the second considers that all customers must have the same “weight” in the final average.