3 Replies Latest reply on Jul 6, 2012 12:15 PM by Jonathan Drummey

    How do I apply a custom distirbution to a count of items?

      I have a database of software defects.  We rank each defect into one of 6 categories.  From historical data, I know the distribution of software defects to categories.  I would like to use Tableau to find software defects that are unranked (this will be a changing number over time) and then apply a distribution to this number to predict how many category 1 through 6 I have.  This is helpful for me because we have requirements on how many category 1-6 software defects we can have open at our milestones.

        • 1. Re: How do I apply a custom distirbution to a count of items?
          Jonathan Drummey

          Hi Lee,

           

          Without some sample data and a better idea of your desired output it's hard to give an exact answer. For example, in your projection do you want to show just one number that is actual+predicted for each category, or do you want to show actuals and predicted integrated into the same chart or graph, or completely separately? There are ways in Tableau to achieve each result, it would be helpful to have more information to go on.

           

          Cheers,

           

          Jonathan

          • 2. Re: How do I apply a custom distirbution to a count of items?

            Here is an example.  I have 500 records where the impact category has not been set (NULL).  There are 6 categories.  I have 55,000 records who have their impact category set and that let me to the following distribution.

             

            2% of the records are category 1

            8 % of the records are category 2

            30% of the records are category 3

            35% of the records are category 4

            15% of the records are category 5

            10% of the records are category 6

             

            I would like to apply this distribution to the 500 records where the impact category is not set (i.e impact category = null).

             

            Does that make sense?  I dont need to show in the same graph waht the impact category is once it gets a value.  I really just want to know what I can expect to see.  Overtime, additional records will be added that do not have their impact category assigned and I would like the data to be updated so that I can see how many records I can expect in each category.

            • 3. Re: How do I apply a custom distirbution to a count of items?
              Jonathan Drummey

              Hi Lee,

               

              Probably the easiest would be to create seven calculated fields:

               

              Total Unset Impact Category: SUM(IF ISNULL([Impact Category] THEN 1 END)

              Unset Category 1 Estimate: .02 * [Total Unset Impact Category]

              Unset Category 2 Estimate: .08 * [Total Unset Impact Category]

              ...

              Unset Category 6 Estimate: .1 * [Total Unset Impact Category]

               

              Then in the view you could use Measure Names/Values to show all the values. If you need more help, posting some sample data and/or a packaged workbook (.twbx) would be helpful.

               

              Cheers,

               

              Jonathan