Forum Discussion
Solved
calculated column based on column content
Hi, could you help me come up with a calculated column that shows "Failed VOE", when the next phase after Verification of Effectiveness/Verification of Effectiveness approval is Initial response...
modify to this :
cc = if( (tbl[PHASE NAME] = "Verification of Effectiveness" || tbl[PHASE NAME] = "Verification of Effectiveness approval") && selectcolumns( offset ( 1 , summarize ( tbl , tbl[PHASE NAME] , tbl[PHASE TICK_ID-1]) , orderby( tbl[PHASE TICK_ID-1] , asc ) , PARTITIONBY(tbl[INTERNAL_AUDIT_RESPONSE_NUMBER]) ), "@PHASE NAME" , tbl[PHASE NAME] ) = "Initial response" , "Failed VOE" )let me know if this helps /
If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠
I might need your help a bit more. this is working, but i also need the condition that the AR number needs to be the same. in my screenshot you see that there is a column with a Audit response number. could you help me integrate it in the formula? Again, thanks A LOT for your help.
modify to this :
cc =
if(
(tbl[PHASE NAME] = "Verification of Effectiveness" || tbl[PHASE NAME] = "Verification of Effectiveness approval") &&
selectcolumns(
offset ( 1 ,
summarize ( tbl , tbl[PHASE NAME] , tbl[PHASE TICK_ID-1]) ,
orderby( tbl[PHASE TICK_ID-1] , asc ) ,
PARTITIONBY(tbl[INTERNAL_AUDIT_RESPONSE_NUMBER])
),
"@PHASE NAME" , tbl[PHASE NAME]
) = "Initial response" , "Failed VOE" )
let me know if this helps /
If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠
i have added Phase Name to the summarize table as well. with that it works perfectly!! amazing, thank you!!