2 Replies Latest reply on Nov 14, 2013 5:23 PM by Jason Carian

    Need Help: Count(d) for system serial #'s across different software versions

    Jason Carian

      I'm having some trouble using the Count Distinct function, and I want to be sure I'm using it right.

       

      Basically, I have an Excel spreadsheet which contains hundreds of thousands of rows of data. Each row is a report back from a customer system, and each row contains data in Columns. The most important Columns are: Serial#, App Version, Date.

       

      A customer system is identified by the Serial #, and each system can send hundreds (even thousands) of reports back into my Excel file. There are multiple App Versions (let's say, v1 thru v4). Applications were released at different time periods. For instance, in early 2011, customers may have only been running v1 (meaning all the data from early 2011 in my report shows Serial #s with v1), but as we shipped newer versions, some customers migrated to these new versions.

       

      What I'd like to do is get a distinct system count for specific Serial #s based on App Version over time. I'd like this data to maintain a count of all reports based on a monthly basis. For example, for the month of January, I'd like to count all unique Serial #s running v1, v2 etc. However, if a customer switched from v1 to v2 in January (and a report was sent from this system for both versions), I'd like this count to appear for both App Versions.

       

      My problem with CountD right now, is that I think Tableau is blacking out Serial #s based on time, not App Version. For instance, as I look at uptick of our new App Versions, it seems slim because Tableau is blocking out all older systems that have upgraded from older versions.

       

      Ideally, I'd like a monthly (or even quarterly) total of all unique Serial #s (once again, based on App Version). For different App Versions, Tableau will not block out serial #s used with previous applications.

       

      Your help is appreciated

        • 1. Re: Need Help: Count(d) for system serial #'s across different software versions
          Jim Wahl

          The best solution depends somewhat on how you want to display this data, and a packaged workbook with your goal and some example data would be helpful.

           

          But, by default, Tableau calculates measures for every combination of every member of every dimension in the view.

           

          Example 1: You drag Date (Month) to the Columns shelf and Serial# to the Rows shelf and select COUNTD. Tableau will calculate the distinct count of serial numbers for each month.

           

          Example 2: Add App Version to the Row shelf. Tableau will generate another set of marks for each combination of serial#, Month, and App Version. If App Version is added to the Color button, you'll get  a colored line.

           

          What gets a little complicated is totaling the rows in example 2. Tableau's Grand Totals (Analysis > Totals > Grand Totals) work by calculating the measure at a higher level of detail (as if you removed the dimensions to the left of the measure), so adding grand total in Example 2 will give you the same result as Example 1, not the sum of the rows in Example 2.

           

          To get the sum of the rows in Example 2, you can use a table calc: Total =

          IF FIRST() == 0 THEN

               WINDOW_SUM(COUNTD(Serial#))

          END

           

          Make sure App Version is on the level of detail shelf and add Total. Right-click the field and select Compute Using App Version. COUNTD() will generate the counts as in Example 2 and WINDOW_SUM() will add all of the values in the App Version dimension.

           

          Tableau will calculate this for every member of the App Version dimension. If you have three versions, you'll get three copies of the WINDOW_SUM(...), which will all be identical. The IF FIRST()==0 solves this problem by only executing the function for the first row.

           

          Jim

          • 2. Re: Need Help: Count(d) for system serial #'s across different software versions
            Jason Carian

            Thanks Jim.

             

            I've followed your instructions. Columns show MONTH, and Rows show App Version. I added Serial # to the row shelf and selected CountD.

             

            However, when I go and look at the raw data in Excel, the numbers don't add up. For instance, I see 50+ unique Serial #s for a certain month in Excel, while Tableau only shows ~10.