1 Reply Latest reply on May 28, 2016 6:10 PM by Alex Blakemore

    Run two aggregations on a dataset

    Donald Pinkus

      I have a dataset with 1 row for every day a user had conversions.  In Tableau, I want to create a table that shows # of days with conversions, and # of users.  How can I create this table (shown as Example result below)?

       

      Example dataset:

      user_idconversion_date
      11/1/2016
      11/2/2016
      11/7/2016
      21/1/2016
      21/3/2016
      21/9/2016
      31/1/2016
      41/3/2016
      41/4/2016
      41/5/2016


      Here we can see User 1 has 3 days with conversions, User 2 has 3 days, User 3 has only 1 day, etc. 

       

      Example result:

      days_with_conversionsuser_count
      153
      249
      340
      437
      529
      614

       

      In SQL, I would write the query like this:

      # Count the number of users that converted a particular number of days.
      SELECT

      days_with_conversions,

      COUNT(1) as user_count

      FROM (
           # Get a count of the days each user converted.

          SELECT
           user_id,

          COUNT(1) as days_with_conversions,

          FROM daily_user_metrics

          GROUP by user_id

      )