Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
simin_au
Contributor III
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)

        )

    ))

)

 

Any suggestion, please?

Labels (4)
1 Solution

Accepted Solutions
simin_au
Contributor III
Contributor III
Author

Thank you so much! It worked 🙂

View solution in original post

6 Replies
vincent_ardiet_
Specialist
Specialist

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

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

        )

simin_au
Contributor III
Contributor III
Author

simin_au_0-1701446454380.png

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

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

simin_au
Contributor III
Contributor III
Author

Thank you so much! It worked 🙂