topic count with condition in QlikView

count with condition

Tue, 25 Aug 2009 16:21:28 GMT

i have a database of measured data and want to calculate Cp and Cpk - values and count critical values (value<1.33)

i have different parts ("TKZ") with different features ("M_NR") with multiple measured data ("value") for every fearure and upper and lower tolerances ("U_TOL" & "L_TOL") for the features

CP = (U_TOL-L_TOL) / (6 * stdev(value))

it is no problem to list CP with M_NR as dimension -> i get the cp-value for every feature

now my problem is to count the number of cp-values that are lower then 1.33

i tried: with TKZ as dimension

count( if [ {(U_TOL-L_TOL)/(6*stdev(value)}<1.33 , M_NR] )

i also tried to load it in the script.. but its always the problem to connect the values with the features..

the formula always calculates the "stdev(value)" above all the values from every feature and not only for the selected..

AW:count with condition

rbecher — Tue, 25 Aug 2009 17:03:32 GMT

try this:

if( (U_TOL-L_TOL)/(6*stdev(value)<1.33, count(M_NR), 0)

count with condition

amit_shetty78 — Tue, 25 Aug 2009 17:29:32 GMT

Or try this:

sum( if( (U_TOL-L_TOL)/(6*stdev(value)) < 1.33, 1, 0) )

count with condition

Tue, 25 Aug 2009 18:20:57 GMT

sorry but both suggestions wont work..

the problem is that U_TOL, L_TOL and value exsist for a lot of features and parts..

and when i make a table with TKZ as first dimension and the formula as second column it is not connected to M_NR, but when i take M_NR also as dimension i get a "1" in every row..

AW:Re: count with condition

rbecher — Tue, 25 Aug 2009 18:27:13 GMT

Then this is a data model issue. You need a fact table with a key of every dimension (star schema). With this structure you can easily aggregate over dimension hierarchies.

- Ralf

count with condition

Wed, 26 Aug 2009 18:44:55 GMT

i found the answer for my problem:

it works with the aggr( ) funktion:

count( if( aggr( (U_TOL-L_TOL) / (6*stdev(value) ), TKZ, M_NR) > 1.33, M_NR ) )