Survey_A1 = 2
Survey_A2 = 3
Survey_A3 = 1
Survey_A4 = 5
Survey_A5 = 2
Survey_A6 = 1
Survey_A7 = 3
Have three distribution ranges;
1. Any Survey_A < 2
2. Any Survey_A >= 2 AND < 5
3. All Survey_A = 5
New logic from snoyes, the data set above is still showing up in range #1 and range #2, when it should be only in range #1.
SUM(
IF(
GREATEST( Survey_A1, Survey_A2, Survey_A3, Survey_A4, Survey_A5, Survey_A6, Survey_A7 ) >= 2
AND LEAST(
IF( Survey_A1 = 0, 6, Survey_A1),
IF( Survey_A2 = 0, 6, Survey_A2),
IF( Survey_A3 = 0, 6, Survey_A3),
IF( Survey_A4 = 0, 6, Survey_A4),
IF( Survey_A5 = 0, 6, Survey_A5),
IF( Survey_A6 = 0, 6, Survey_A6),
IF( Survey_A7 = 0, 6, Survey_A7)
) < 5
,1,0)
) AS 'ANY Q >= 2 < 5',
With the data above, how would I write a select expression that would work for distribution range #2? Here is my existing logic.
SUM(
IF((
( Survey_A1 != 0 AND Survey_A1 >= 2 AND Survey_A1 != 5 ) OR
( Survey_A2 != 0 AND Survey_A2 >= 2 AND Survey_A2 != 5 ) OR
( Survey_A3 != 0 AND Survey_A3 >= 2 AND Survey_A3 != 5 ) OR
( Survey_A4 != 0 AND Survey_A4 >= 2 AND Survey_A4 != 5 ) OR
( Survey_A5 != 0 AND Survey_A5 >= 2 AND Survey_A5 != 5 ) OR
( Survey_A6 != 0 AND Survey_A6 >= 2 AND Survey_A6 != 5 ) OR
( Survey_A7 != 0 AND Survey_A7 >= 2 AND Survey_A7 != 5 ))
,1,0)
) AS 'ANY Q >= 2 < 5',
Here is the expression for < 2
SUM(
IF((
( Survey_A1 != 0 AND Survey_A1 < 2 ) OR ( Survey_A2 != 0 AND Survey_A2 < 2 ) OR
( Survey_A3 != 0 AND Survey_A3 < 2 ) OR ( Survey_A4 != 0 AND Survey_A4 < 2 ) OR
( Survey_A5 != 0 AND Survey_A5 < 2 ) OR ( Survey_A6 != 0 AND Survey_A6 < 2 ) OR
( Survey_A7 != 0 AND Survey_A7 < 2 ) )
,1,0)
) AS 'ANY Q < 2',
Here is the expression for = 5
SUM(
IF((
(Survey_A1 = 0 OR Survey_A1 = 5 ) AND (Survey_A2 = 0 OR Survey_A2 = 5 ) AND
(Survey_A3 = 0 OR Survey_A3 = 5 ) AND (Survey_A4 = 0 OR Survey_A4 = 5 ) AND
(Survey_A5 = 0 OR Survey_A5 = 5 ) AND (Survey_A6 = 0 OR Survey_A6 = 5 ) AND
(Survey_A7 = 0 OR Survey_A7 = 5 ) )
,1,0)
) AS 'ALL Q = 5',