Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
dwmorris
New 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
New Contributor II

Re: Quality Ratio Calculation

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.

5 Replies
OmarBenSalem
Esteemed Contributor

Re: Quality Ratio Calculation

Hi Dean,

Can you share your app?

To work with?

dwmorris
New Contributor II

Re: Quality Ratio Calculation

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
Esteemed Contributor

Re: Quality Ratio Calculation

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

Re: Quality Ratio Calculation

Thank you Omar.

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

Dean Morris

Quality Engineer

dwmorris
New Contributor II

Re: Quality Ratio Calculation

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.