count with condition

[#1]

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..

[rbecher]

try this:

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

[amit_shetty78]

Or try this:

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

[#4]

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..

[rbecher]

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

[#6 · Accepted solution]

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 ) )