calculated column based on column content

[alevandenes]

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? the Phase_tick_id are giving the correct order of the phases.

Thanks a lot!! forever grateful to whom can help me

Kind regards,

Alessandra

[Daniel29195]

@alevandenes 

write the following calculated column : 

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 ) 
),
"@PHASE NAME" , tbl[PHASE NAME]

) = "Initial response" , "Failed VOE" ) 

let me know if that works for you .

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! 🤠

[alevandenes]

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.

[Daniel29195 · Accepted solution]

@alevandenes 

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! 🤠

[alevandenes]

i have added Phase Name to the summarize table as well. with that it works perfectly!! amazing, thank you!!