Quality Ratio Calculation

[dwmorris]

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

[OmarBenSalem]

Hi Dean,

Can you share your app?

To work with?

[dwmorris]

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.

[OmarBenSalem]

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,

[dwmorris]

Thank you Omar.

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

Dean Morris

Quality Engineer

[dwmorris · Accepted solution]

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.