Announcements
cancel
Showing results for
Did you mean:
Contributor III

## Control Chart IF condition

Hi,

I need to create a Control P Chart in Qlik sense. The expression codes for UCL and LCL works. However, when I add a new condition to the expression, it doesn't work. It doesn't show any error but doesn't produce any results too.

For example, the following expression create LCL:

=if(

sum(x.denomerator) = 0,

null(),

(

(SUM(total[x.numerator]) / SUM(total[x.denomerator]))

- 3 * SQRT(

(SUM(total[x.numerator]) / SUM(total[x.denomerator]))

* (1 - (SUM(total[x.numerator]) / SUM(total[x.denomerator])))

/ SUM(x.denomerator)

)

)

)

But when I add the if condition of "MAX(0, (.... " it doesn't produce any results, as below:

=IF(

SUM([x.denomerator]) = 0,

NULL(),

MAX(0, (

(SUM(total[x.numerator]) / SUM(total[x.numerator]))

- 3 * SQRT(

(SUM(total[x.numerator]) / SUM(total[x.denomerator]))

* (1 - (SUM(total[x.numerator]) / SUM(total[x.denomerator])))

/ SUM(x.denomerator)

)

))

)

Labels (4)

• ### Visualization

1 Solution

Accepted Solutions
Contributor III
Author

Thank you so much! It worked 🙂

6 Replies
Specialist

Are you mixing MAX and RANGEMAX?
MAX accept 2 arguments but the second one is the rank, MAX(0,whatever) will return NULL.

Contributor III
Author

It's a P-Chart. The min LCL could be 0 (no minus zero), and the max should be 1 (not more than one).

In summary, the breakdown of expressions for rate, UCL and LCL is:

 A1: =SUM(total[x.numerator]) / SUM(total[x.denomerator]) A2: =1 - (SUM(total[x.numerator]) / SUM(total[x.denomerator])) A3: =SQRT(A1 * A2 / SUM(x.denomerator)) A4: =3 * A3 A5: =A1 + A4 A6: = A1 - A4 A7: =MIN(1, A5) A8: =Max(0, A6)
Specialist

So, yes, you are mixing MAX and RANGEMAX.
Use:

RANGEMAX(0, (

(SUM(total[x.numerator]) / SUM(total[x.numerator]))

- 3 * SQRT(

(SUM(total[x.numerator]) / SUM(total[x.denomerator]))

* (1 - (SUM(total[x.numerator]) / SUM(total[x.denomerator])))

/ SUM(x.denomerator)

)

Contributor III
Author

I'm trying to replicate this calculation (in excel) in qlik sense for the P-Chart

Specialist

Sure, but again, MIN and MAX in QS are functions to aggregate data.
If you want to keep the min or the max of a series of number, you should use RANGEMIN or RANGEMAX (see https://help.qlik.com/en-US/qlikview/May2023/Subsystems/Client/Content/QV_QlikView/ChartFunctions/Ra...)

Contributor III
Author

Thank you so much! It worked 🙂