5 Replies Latest reply on Apr 1, 2017 9:03 AM by Daniel Berlin

    Running count distinct

    Ubair Usmani

      Hi All,

       

      I have data of user IDs of users who have opened emails sent to them with particular dates. I have created a chart of running total of count of distinct users who opened the emails by date.

      I want to create a graph that shows running distinct count which count distinct users as mentioned below:

       

      January - unique count of users in January

      February -  unique count of users for the period January to February

      March - unique count of users for the period January to March

      April - unique count of users for the period January to April

       

      Can you help me to get the desired result?

        • 1. Re: Running count distinct
          Walt Reed

          Hey Ubair,

          You will want to drag the users onto the viz, and calculated with COUNTD(). Then, add a Quick Table Calculation of Running Total, and that should get you the desired result. See screenshot below:

           

          Walt

          • 2. Re: Running count distinct
            Ubair Usmani

            Hello Walt,

             

            Thanks for the information. Running total will provide the sum of unique logins in previous months + unique logins in concerned month.

            For example running total for February will be the sum of count distinct of January and count distinct of February. But there is a possibility that the person who login in January, login again in February. So for February we need count distinct of the period Jan - Feb.

             

            Hope this makes sense.

             

            Regards,

            Ubair Usmani

            • 3. Re: Running count distinct
              Walt Reed

              Hey Ubair,

              My apologies for the misunderstanding! I see what you're trying to

              accomplish. Not being at my computer, I'll attempt to get you going in the

              right direction.

               

              First, I would do an LOD calc to determine the initial login month:

              {FIXED  : MIN()}

               

              Then you would add this field to the viz, then do a COUNTD(), then

              add a Quick Calculation of running total.

               

              I will try this when I get back to the office to make sure it works.

               

              Walt

               

              EDIT: I checked when I got to the office and this solution worked.

              • 4. Re: Running count distinct
                Bora Beran

                You can compare their first activity date with current month and count it as 1 otherwise 0 and then do a running sum over it.

                 

                E.g. SUM(IF {FIXED [customer name] : min(Date)}==Date then 1 else 0)

                 

                If you drag this field into the view that has months as dimensionality and e.g. if customer made first purchase on january 2nd but then bought more on january 5th  and march 2nd etc. except january 2nd they will all count as 0. so when you do a sum, they won't have any effect. It will count as 1 in January and 0 in all other months.

                 

                Because of this when you do a running sum, it won't double count existing customers.

                • 5. Re: Running count distinct
                  Daniel Berlin

                  Hi Ubair,

                   

                  I have a workbook on Tableau Public that illustrates how to use the solution Walt proposed.  The captions and annotations in the workbook explain the ideas behind this method.

                   

                  https://public.tableau.com/views/RunningCountD/Story1?:embed=y&:display_count=yes

                   

                  I hope this is helpful!