Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Thank you so much! It worked 🙂
Are you mixing MAX and RANGEMAX?
MAX accept 2 arguments but the second one is the rank, MAX(0,whatever) will return NULL.
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) |
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)
)
I'm trying to replicate this calculation (in excel) in qlik sense for the P-Chart
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...)
Thank you so much! It worked 🙂