1 Reply Latest reply on Jul 19, 2013 12:37 PM by Mark Holtz

    Counting months with no data

    Xavier P

      I have a list of users who login monthly to a website.  Now I'd like to count the number of months where is there is no login over the last 13 months.

       

      So for example Carla has failed to login 8 months and Tony 4 months.

       

      How can I count the months with no login?

        • 1. Re: Counting months with no data
          Mark Holtz

          Hi Xavier,

           

          Unfortunately, the information you're interested in actually pertains to that which is missing from your data source... There are generally two paths to take here:

          1) Deal with the missing data within Tableau (this concept is known as Domain Padding, Domain Densification, or Domain Completion)

          2) Modify your data before pulling into Tableau so that you have access to all possible date-user combinations.

           

          I find the first option to be incredibly complex, with lots of advanced formulas and confusing concepts.

          Ultimately, I'd recommend you incorporate custom SQL to make your data able to answer the question as soon as you pull it into Tableau.

           

          To do this, you would need to find a source containing all time-intervals values you are interested in... if you only care about a month-level, you just need a source with all months present. You would then need to CROSS JOIN that table to a table of all your users, and then you can create a yes/no field to indicate whether each user did or did not log in for each month.

           

          Using data from your attached example, it'd look something like this:

          DateUserLog In?
          4/1/2012Carla1
          5/1/2012Carla1
          6/1/2012Carla1
          7/1/2012Carla1
          8/1/2012Carla1
          9/1/2012Carla0
          10/1/2012Carla0
          11/1/2012Carla0
          12/1/2012Carla0
          1/1/2013Carla0
          2/1/2013Carla0
          3/1/2013Carla0
          4/1/2013Carla0
          4/1/2012Jacques0
          5/1/2012Jacques0
          6/1/2012Jacques0
          7/1/2012Jacques0
          8/1/2012Jacques0
          9/1/2012Jacques0
          10/1/2012Jacques0
          11/1/2012Jacques0
          12/1/2012Jacques1
          1/1/2013Jacques0
          2/1/2013Jacques1
          3/1/2013Jacques1
          4/1/2013Jacques1

           

          You could certainly make the data more robust (create a record for each day there was a login, add more measures, etc.) But as long as the rows with 0's exist, you will now have access to the information within Tableau without having to do anything supremely tricky.

           

          If you want to pursue the option within Tableau, Jonathan Drummey's blog Drawing with Numbers has some excellent information and insights on tackling that challenge.

           

          Hope that helps!