Requires the Total value of this condition.

[Paknanarn23]

Want the total value as shown in the red box. In this condition, what should I do?

This is the code. What should I do?
if(if(([Average (Left point)]+[Average (Middle point)]+[Average (Right point)])/3 > SpecMax or
([Average (Left point)]+[Average (Middle point)]+[Average (Right point)])/3 < SpecMin,'Rej.','Acc.')='Rej.' and If(
Max(Aggr(If(RangeMax(LeftPointA, LeftPointB, LeftPointC, LeftPointD, MiddlePointA, MiddlePointB, MiddlePointC, MiddlePointD, RightPointA, RightPointB, RightPointC, RightPointD) > SpecMax + (RangeSum(SpecMax, SpecMin) / 2) / 2
OR
RangeMin(LeftPointA, LeftPointB, LeftPointC, LeftPointD, MiddlePointA, MiddlePointB, MiddlePointC, MiddlePointD, RightPointA, RightPointB, RightPointC, RightPointD) < SpecMin - (RangeSum(SpecMax, SpecMin) / 2) / 2,1, 0),
WO_WorkOrderNo, Timestamp
)
) = 1,
'Invalid data',
'Valid data'
) = 'Valid data',Count(Aggr(WO_WorkOrderNo,Timestamp)))

[diegozecchini]

Hi!
From the image and the provided Qlik code, you want to calculate the Total value that meets the specific conditions of rejection (Rej.) and validation (Valid data).

I suggest review and simplify the logic: The provided condition has multiple layers. The logic can be broken down into:

-Averaging the left, middle, and right points to check if they exceed SpecMax or are below SpecMin.
-Validating ranges using RangeMax and RangeMin.
-Aggregating results for each WO_WorkOrderNo and Timestamp.

Ensure Correct Aggregation:

The final total value must count WO_WorkOrderNo only when the condition evaluates to 'Valid data'.

Try this:

Sum(
Aggr(
If(
If(
(
([Average (Left point)] + [Average (Middle point)] + [Average (Right point)]) / 3 > SpecMax
OR
([Average (Left point)] + [Average (Middle point)] + [Average (Right point)]) / 3 < SpecMin
),
'Rej.',
'Acc.'
) = 'Rej.'
AND
If(
Max(
Aggr(
If(
RangeMax(
LeftPointA, LeftPointB, LeftPointC, LeftPointD,
MiddlePointA, MiddlePointB, MiddlePointC, MiddlePointD,
RightPointA, RightPointB, RightPointC, RightPointD
) > SpecMax + (RangeSum(SpecMax, SpecMin) / 2) / 2
OR
RangeMin(
LeftPointA, LeftPointB, LeftPointC, LeftPointD,
MiddlePointA, MiddlePointB, MiddlePointC, MiddlePointD,
RightPointA, RightPointB, RightPointC, RightPointD
) < SpecMin - (RangeSum(SpecMax, SpecMin) / 2) / 2,
1,
0
),
WO_WorkOrderNo,
Timestamp
)
) = 1,
'Invalid data',
'Valid data'
) = 'Valid data',
1,
0
),
WO_WorkOrderNo,
Timestamp
)
)

[Paknanarn23]

HI! diegozecchin

I tried but the value still doesn't show. I have a code that makes it count the values ​​as 1, but it doesn't allow me to combine them into a single value like this. Please help me at

if(
if(
if(if(([Average (Left point)]+[Average (Middle point)]+[Average (Right point)])/3 > SpecMax or
([Average (Left point)]+[Average (Middle point)]+[Average (Right point)])/3 < SpecMin,'Rej.','Acc.')='Rej.' and If(
Max(Aggr(If(RangeMax(LeftPointA, LeftPointB, LeftPointC, LeftPointD, MiddlePointA, MiddlePointB, MiddlePointC, MiddlePointD, RightPointA, RightPointB, RightPointC, RightPointD) > SpecMax + (RangeSum(SpecMax, SpecMin) / 2) / 2
OR
RangeMin(LeftPointA, LeftPointB, LeftPointC, LeftPointD, MiddlePointA, MiddlePointB, MiddlePointC, MiddlePointD, RightPointA, RightPointB, RightPointC, RightPointD) < SpecMin - (RangeSum(SpecMax, SpecMin) / 2) / 2,1, 0),
WO_WorkOrderNo, Timestamp
)
) = 1,
'Invalid data',
'Valid data'
) = 'Valid data',Count(Aggr(WO_WorkOrderNo,Timestamp))) >0,1,0)=1,
Count(DISTINCT Aggr(
IF(Count(WO_WorkOrderNo) > 0, WO_WorkOrderNo),
Timestamp
))

)

[diegozecchini]

Hi I sugggest you verify that fields like SpecMax, SpecMin, LeftPointA, etc., have no null or invalid values.
Could you check for unique combinations of WO_WorkOrderNo and Timestamp to avoid duplication?

In those cases a a good tip is to split the logic into parts and display intermediate results in separate table columns to ensure each part is working correctly.