1 Reply Latest reply on Feb 11, 2014 7:43 PM by Joshua Milligan

    distinct count of devices by version per day when devices do not have entry in table everyday

    R I

      I’m really hoping someone can help me with a little problem I have run into with tableau reports. I need to create a report that gives me distinct count of devices by version for each day. My base table does not have an entry for every device every day as not all devices upgrade to a different version every day. So  D1 might have an entry in the table on the 1st of Jan and the version V1 and then on 10th Jan for version V2 and we know that from the 1st to the 10th  D1 stayed on the same version V1. However I am not sure how to plot this in Tableau. Would anyone know how to solve this?  I have attached a spreadsheet with sample data and chart for better understanding. This is a critical report and any help would be much appreciated!!

        • 1. Re: distinct count of devices by version per day when devices do not have entry in table everyday
          Joshua Milligan

          R I,

           

          This can be done in Tableau, but is not trivial when the data is sparse.  Below is an example of how you can pad the data in Tableau to accomplish what you want.

           

          However, I would also suggest checking out this thread: Count the number of leave days taken by weekday | Tableau Support Community and note Jonathan Drummey's alternatives for solving these kinds of problems, especially using a cross-product join.  Solving the problem in the back-end (either by creating a permanent/semi-permanent data structure or using custom SQL) can make this a whole lot easier in Tableau.

           

          But, because that's not always an option (and because it's fun to work through these), here's an option in Tableau:

           

          First, you'll need to pad the data.  There are a couple of ways to do this in Tableau.  Here, I've used the date field, and turned on "Show Missing Values" (right click the field on Columns for the option).

          0.png

           

          Here's the calculated field that takes advantage of the ability to address the padded data to "carry" the most recent values across the table.

          -1.png

           

          Finally, I rearranged the view, and created a few more calculated fields to add up the counts of each version.  I found this necessary because, as a table calc, the Version field could not further partition the data.

          1.png

           

          I'd be more than happy to answer any questions you might have.  Feel free to contact me.

           

          Regards,

          Joshua