Forum Discussion

alevandenes's avatar
alevandenes
Helper IV
2 years ago
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? 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

Need Help
  • Daniel29195's avatar
    Daniel29195
    2 years ago

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

4 Replies

  • Daniel29195's avatar
    Daniel29195
    Community Champion
    2 years ago

    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's avatar
      alevandenes
      Helper IV
      2 years ago
      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's avatar
        Daniel29195
        Community Champion
        2 years ago

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