Filter Cascading

[#1]

Hello,

This is my first ever QlikView application, so please be patient with me

I am calculating the ratio between the defective parts over the number of produced parts in a factory. My data is in one table and the columns are:

Metric, Month, Units,DefectReason

The metric column could be one of 3 values "Produced", "Defective", and "Scrap" and the DefectReason is always "NA" when the metric is "Produced". I want to create a line chart plotting the month on the x axis, and the ratio between the defective units (sum(units) Where Metric="Defective" ) and produced units (sum(units) Where Metric="Produced" )  on the y axis. I also want to see a line for each DefectReason. I am using the following set expression

sum(Units)/sum({<Metric={"Produced"},DefectReason=>}Units)

The chart is automatically filtering the DefectReason to "NA" and I think the reason is because I specify "Produced" as my denominator, which always corresponds to a DefectReason of "NA". I basically want QlikView to make the shown defect reasons unaffected by the DefectReason associated with the denominator calculation .I tried to fix it by stating that the DefectReason could be any value as you can see in the denominator set expression, but that did not give me the required result. I am always getting 100% as my ratio across all the months for one defect reason; "NA".

Thanks!

[Anonymous]

I don't know if I understand your question...

I think you should create one expression for each DefectedReason like this:

sum({<Metric={'Defective'},DefectReason={'First_DefectedReason'}>}Units)/sum({<Metric={"Produced"}>}Units)

so, you will have the ratio for each DefectedReason.

If it is not correct, please attach your data with the expected output.

Thanks,

Elena