Control Chart IF condition

[simin_au]

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)

        )

    ))

)

Any suggestion, please?

[vincent_ardiet_]

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

[simin_au]

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)

[vincent_ardiet_]

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)

        )

[simin_au]

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

[vincent_ardiet_]

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

[simin_au · Accepted solution]

Thank you so much! It worked 🙂