5 Replies Latest reply: Apr 12, 2017 3:22 PM by Dean Morris RSS

    Quality Ratio Calculation

    Dean Morris

      I am trying to find a way to compare quality data over time for multiple models of products.

      I have a Production table that has the production date and model number for each unit produced.

      I also have a  Defect table that has the defect quantity for each unit produced.

       

      I want to calculate and graph a defect ratio, (defects/units produced), by various time frames (daily, weekly, monthly, quarterly, yearly).

      I may also want a view to compare 2 models' ratios side by side.

       

      I have the Production dates and Defect Dates already setup with Auto-calendar fields.

       

      I'm struggling with figuring our how to aggregate & calculate by time period.

      Can anyone provide some suggestions or samples? 

      I'm using Qlik Sense Desktop.

       

      Thank you in advance,

      Dean

        • Re: Quality Ratio Calculation
          omar bensalem

          Hi Dean,

           

          Can you share your app?

          To work with?

            • Re: Quality Ratio Calculation
              Dean Morris

              I can't.  Too much proprietary info.

               

              My data would look like this:

               

              Production Data :

              PROD_ DATE, Model number, SERIAL

              3/1/17 2:42pm, A, 1001

              3/1/17 2:43pm, A, 1002

              3/1/17 2:44pm, B, 1001

              3/1/17 2:45pm  B, 1002

              3/2/17 2: 46pm, C, 1001

              ...

               

              Defect Data:

              DEFECT_DATE, Model Number, Serial

              3/5/17, A, 1001

              3/6/17, A, 1002

              ...

               

              I want to count the unique serials for each model per desired production time period (day, week, month, etc).

              Similar for the defects.

               

              Then calculate:

              Model, Month, Defect Count, Production Count, (Defect Count/Production count*100)

               

              Also show in a graph:

              Yr_Month                          2017_1,             2017_2,                    2017_3, ...

              Model A                           Defect count/Production count*100 for each month

              Model B                           Defect count/Production count*100 for each month

               

              Note: I don't need to join anything by serial number.   I just need to count the unique serials in a given time period.

              Thanks again.

                • Re: Quality Ratio Calculation
                  omar bensalem

                  Hi Dean,

                   

                  I invite you to read this carefully; I really think that It could be of a considerable help:

                   

                  YTD, MTD issue

                   

                  If you still find problems handling this, don't hesitate to 'call back'.

                   

                  Omar,

                    • Re: Quality Ratio Calculation
                      Dean Morris

                      Thank you Omar.

                      I’ll study this a bit and let you know how it goes.

                       

                       

                      Dean Morris

                      Quality Engineer

                        • Re: Quality Ratio Calculation
                          Dean Morris

                          Omar,

                          I have studied your article and other set analysis videos and think I have made good progress.

                           

                          For each year I have created the following expression to generate my quality ratio:

                           

                          count({$<BLFYRC={2013}>}BUCLMN)/count({$<[REPORTDATEYEAR]={2013}>}BUSERL2)

                          ie:  Ratio= (Count total defects for 2013)/(Count total serial numbers for 2013)

                           

                          Are there any problems with dividing one set expression for "defect count" by the other set expression "serial number count"?  They come from different tables.

                           

                          Everything seems to filter properly using normal selections.

                          Note: I ran into much trouble trying to filter the year using derived calendar fields (still a known bug?)  so I created a new field using a date function in the import script. Now my filters work.