1 Reply Latest reply on Aug 25, 2013 10:56 AM by Joshua Milligan

    Count Distinct values over various weeks

    Abul Hasan


      Hi,


      I am trying do a calculation to see unique visitors over various weeks. Below is an example.

       

      Week     ID

      1          1

      1          2

      2          3

      2          2

       

      I want to count if a person is in week 1 and 2.

       

      The answer should be

       

      ID     Week1  week2     both

      1     1               0          0

      2     1               1          1

      3     0               1          0

       

      Thanks for your help.

        • 1. Re: Count Distinct values over various weeks
          Joshua Milligan

          Abul,

           

          This is a problem that doesn't have an easy solution.  You might take a look at this thread in which some of the Tableau Zen Masters discuss different approaches and the pros and cons of each.

           

          How to count distinct users on a running period

           

          I've attached a workbook that gives a possible solution -- and although it works for the simple data set you provided (note that I added a row to give me something for the "both" column), it is just a starting point and the ultimate solution is likely more complex.  Ultimately, with a large or complex initial data set, you'd probably want to shape your data on the front end prior to connecting Tableau.

           

          Distinct Data.twbx.png

           

          Regards,

          Joshua

           

          Distinct Data 1.twbx.png