5 Replies Latest reply on Nov 19, 2014 1:30 PM by Matt Lutton

    need help calculating a defect rate (ratio)

    paul morgan

      Hi everybody,


      I am trying to create a defect rate.  The defect rate will measure the % of defective orders shipped in a time period.


      My defective order rate formula is essentially this: number of re-orders (defective orders)  shipped in a month / by total number of orders shipped in a month.  For example:


      2 re-orders shipped in the month, while 10 total orders shipped = 2/10 = 20% defect rate.


      I have a parent-child relationship between the original (parent) order and the re-order (child).


      And the order numbers i'm working with are called partner order numbers in my dimensions.


      So i need help creating the formula for this calculation.


      In the workbook attached i've created a list of re-orders (for a month).  I want to show a defect rate for this month by dividing the number of reorders (Child Re-order Partner numbers) by the total # of Partner Order numbers shipped.


      I would appreciate any help/insight on how i can accomplish this.


      Many thanks.



        • 1. Re: need help calculating a defect rate (ratio)
          Grayson Deal

          You attached a TWB not a TWBX. To attach a packaged workbook (TWBX) go to file -> Export Packaged Workbook....

          • 2. Re: need help calculating a defect rate (ratio)
            paul morgan

            I tried to create a twbx, but it was taking a long time, over 6 minutes, so i stopped it.  Probably because my data source is very large.  Is it normal for it to take several minutest to create a twbx?  Also, if i just let it finish, i'm thinking it will increase file size dramatically.

            • 3. Re: need help calculating a defect rate (ratio)
              Grayson Deal

              You are probably correct try this method to trim down your data some

              Re: combine two worksheets into a single viz

              • 4. Re: need help calculating a defect rate (ratio)
                paul morgan

                I can't post a twbx due to the fact that there is sensitive customer in formation in the data, and i can't parse it out easily.  Also, it's quite huge.


                The calculation i want to perform is actually quite simple, but i have multiple date and order number dimensions and i think this is what is tripping me up. 


                Would someone be willing to Skype with me so i can show what i'm working with, and then perhaps step through some solutions with me?  That would be fantastic  My Skype id is pmorgan.skype



                • 5. Re: need help calculating a defect rate (ratio)
                  Matt Lutton

                  Hi Paul:

                  I believe most everyone working with data encounters this scenario -- we cannot post our actual data, but there are options for posting sample data that represents the problem you've encountered.

                  For example, you can simply take a subset of your records, randomize any sensitive data -- and use that as the basis for your TWBX file.  This will take you time to figure out, most likely, but it will be worth the effort in the long run, as it will help you create sample workbooks so you can ask questions on Forums like this one.


                  You can export your data from Tableau into Excel, keep a subset of the records (enough to demonstrate your issue), and blind/randomize any sensitive data, remove any unneeded columns from the data, etc.  Extract this newly created Excel data into a packaged workbook, and post the result here as well as the work you've completed on this thus far.  This is particularly easy if you're only working with a single data set.  It becomes more of a challenge when you're working with multiple data sources.


                  Cheers!  Here's some additional helpful info on posting TWBX files here:

                  Re: Guidelines for posting data/tbwx files


                  Another option is to mock up the scenario you've encountered using one of the Sample datasets that ships with Tableau.  Most scenarios can be replicated with these data sets, so you might look at creating an example from the Superstore Sales dataset..


                  Be sure to post a mock up of your expected results, as well.  Best of luck!