6 Replies Latest reply on Aug 8, 2018 3:42 PM by trina fitzpatrick

# calculated field based on group of values

I would like to create a calculated field based on the values of other fields rolling up to a unique value. The data set includes a Color Code with multiple rows for each color.  If the DQ Status for any of the rows for that color is INVALID, then the result would be INVALID, otherwise if all rows are VALID, result would be VALID.

Thank you!!  Appreciate any help. • ###### 1. Re: calculated field based on group of values

See attached.

• ###### 2. Re: calculated field based on group of values

Hi Trina,

Please Create This Calculation and Drag it to Rows.

{FIXED Color_Code:MAX(IF{FIXED Color_Code:MIN(IF DQ_Status="Invalid" THEN 0 ELSE 1 END)}=0 THEN "Invalid" ELSE "Valid" END)}

1 of 1 people found this helpful
• ###### 3. Re: calculated field based on group of values

Hello Trina,

I found a solution using a custom LOD Calculation.  To do this I had to create an if statement to relate invalid to 0 and valid to 1.  This is nested in an Exclude LOD to remove Associate Attribute Name and then fix the RESULT NEEDED  at the Color Code level of detail.  I hope this helps.

IF { FIXED [Color Code]:SUM({ EXCLUDE [Assoc Attribute Nm]: MIN(IF [DQ Status] = "Invalid" then 0 ELSE 1 END)})} = 1 THEN 'VALID' ELSE 'INVALID' END

Please let me know if this works or if any questions.

Thanks and best,

Brandon

• ###### 4. Re: calculated field based on group of values

Well done Deepak, you beat me to it with a slightly alternative formula.  Well done!

• ###### 5. Re: calculated field based on group of values

Thanks..

By the way this will also Work

This is much Simpler

{FIXED [Color Code]:MIN(IF DQ_Status<>"Valid" THEN DQ_Status Else DQ_Status END)}

Deepak

• ###### 6. Re: calculated field based on group of values

THANK YOU!  This worked perfectly.