Forum Discussion

mevans's avatar
mevans
Frequent Visitor
9 years ago
Solved

calculated column that is based on two IF statements

I have three columns that I need to base a new calculated column on where I need to return a value based on two IF statments, one that uses a date and one that finds a particular text within a string.

 

My columns are Date of Sales, Notes.1.1 (this is a date field corresponding to when a customer has called in to customer service), and Notes.2 (this is a text string of user inputted notes)

 

What I'm trying to do is the following calculated column but am not geting the value passed.

 

Customer Cancel =

 

IF(fSales[Notes.1.1]=fSales[Date of Sale]

   && IF(ISBLANK(SEARCH("Cancelled",fSales[Notes.2])),1,BLANK()),

1,BLANK())

 

I'm sure this isn't right. Any help would be appreciated.

  • Sean's avatar
    Sean
    9 years ago

    This should give you 1 if they ordered and then called to cancel the same day (that's basically how I read your question)

     

    Customer Cancel Column = 
IF (
    fSales[Date of Sale] = fSales[Notes.1.1],
    IF (
        ISBLANK ( SEARCH ( "Cancelled", fSales[Notes.2], 1, BLANK () ) ),
        BLANK (),
        1
    ),
    BLANK ()
)

2 Replies

  • Sean's avatar
    Sean
    Community Champion
    9 years ago

    This should give you 1 if they ordered and then called to cancel the same day (that's basically how I read your question)

     

    Customer Cancel Column = 
IF (
    fSales[Date of Sale] = fSales[Notes.1.1],
    IF (
        ISBLANK ( SEARCH ( "Cancelled", fSales[Notes.2], 1, BLANK () ) ),
        BLANK (),
        1
    ),
    BLANK ()
)
    • mevans's avatar
      mevans
      Frequent Visitor
      9 years ago

      Thanks Sean!

       

      That totally worked. Now what if I also wanted to include variations on the spelling of "Cancelled"? Such as Cancel, cncl, cncld.

       

      Is that even possible?