Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
dwmorris
Contributor II
Contributor II

Quality Ratio Calculation

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

1 Solution

Accepted Solutions
dwmorris
Contributor II
Contributor II
Author

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.

View solution in original post

5 Replies
OmarBenSalem

Hi Dean,

Can you share your app?

To work with?

dwmorris
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

Thank you Omar.

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

Dean Morris

Quality Engineer

dwmorris
Contributor II
Contributor II
Author

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.