3 Replies Latest reply on Aug 2, 2012 7:09 AM by Jonathan Drummey

    Calculate a "two-steps" aggregation in Tableau

    Roy Roy

      Hi!

      Is there any way in Tableau to aggregate data and then aggregate again the result (by another field)?

       

      Example:

      I have a list of customers and products they have bought:

      CUSTOMER_IDPRODUCT_ID

      1

      111
      2111
      2123
      7134
      7151
      8524
      8624
      8111

      I want to have a table with count of distinct products for customer as first column, and number of customers with that number as second column.

      Usually when doing it with SQL or Excel, I do it in two steps, first calulating how mant different products each customer bought, and then using this result I calculate how many customers bought each amount of different products.

      In the example above the final result should be:

      number of different products bought by customernumber of customers
      1

      1

      2

      2
      31

       

      That's because one customer (1) bought only one product,

                                 two customers (2,7) bought two products each,  

                                 and one customer (8) bought three products.

       

      I have tried a lot to do that in Tableau with no success. I would be very glad if there is such way.

      Thanks!

        • 1. Re: Calculate a "two-steps" aggregation in Tableau
          Jonathan Drummey

          Hi,

           

          See the attached for one way to go about this, using the following calculated fields:

           

          # of Diff Products Bought by Customer: COUNTD([Product ID])

          # of Customers: IF FIRST()==0 THEN WINDOW_COUNT(COUNTD([Customer ID])) END

           

          In the view, put Customer ID on the Level of Detail Shelf. Then the # of Diff Products calc is brought onto the Rows Shelf, set to Discrete (a blue pill, so it will generate headers), and then click on the blue pill once more to uncheck Ignore in Table Calculations. Finally, the # of Customers can be put on the text shelf (or dragged over the Abc marks) to create a text table, and click on the green pill to set the Compute Using to Customer ID.

           

          There are a couple of tricky bits in this kind of view: In order to have distinct counts of customers and products, both need to be in the overall level of detail (on Rows, Columns, Marks Card, or Pages Shelf) of the view, and in a view like this that's going to return overlapping results. So we use a table calculation with the IF FIRST()==0 to return only one result per partition. The next bit is that you are wanting to partition a measure (the customer count), i.e. generate headers, using the results of another aggregate measure (the count distinct of products per customer). Ordinarily Tableau will only partition based on non-aggregated dimensions. That's where the unchecking "Ignore in Table Calculations" comes in, it enables Tableau to partition by that calculation to generate the desired results.

          • 2. Re: Calculate a "two-steps" aggregation in Tableau
            Roy Roy

            Works like magic! Thanks very much