Forum Discussion

NickzNickz's avatar
NickzNickz
Icon for Helper IV rankHelper IV
2 years ago
Solved

The Output based on multiple condition and Status column

Hi ...  I have table1 with Status , Date1 , Date2 , Date3 , Date4 and column Output. The output is based on the following conditions: 1) If Status is Planning and Date1 exists, Date2 is empty, D...
"Need Help"
  • FreemanZ's avatar
    FreemanZ
    2 years ago

    hi NickzNickz ,

     

    try to add a calculated column like:

    Flag = 
SWITCH(
    TRUE(),
    [date1]<>BLANK() && [date2]=BLANK(), "Planning",
    [date2]<>BLANK() && [date3]=BLANK(), "Advertisement",
    [date3]<>BLANK() && [date4]=BLANK(), "Selling",
    "Closing"
)

     

    it worked like:

FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper User
2 years ago

hi NickzNickz ,

 

try to add a calculated column like:

Flag = 
SWITCH(
    TRUE(),
    [date1]<>BLANK() && [date2]=BLANK(), "Planning",
    [date2]<>BLANK() && [date3]=BLANK(), "Advertisement",
    [date3]<>BLANK() && [date4]=BLANK(), "Selling",
    "Closing"
)

 

it worked like:

  • NickzNickz's avatar
    NickzNickz
    Icon for Helper IV rankHelper IV
    2 years ago

    Hi FreemanZ 

    I've tested the formula and it works.
    Btw, I also added additional criteria because the original data has values I need to reference to make the output more accurate. 

    Calendar Event 3 = 

VAR EventStatus = f_event_list[Status]
VAR Planning = f_event_list[Planning Date]
VAR Advertisement  = f_event_list[Advertisment Date]
VAR Selling = f_event_list[Date Selling Start]
VAR Closing = f_event_list[Original Closing Date]

RETURN
SWITCH(
    TRUE(),
    EventStatus = "Planning" && Planning<>BLANK() && Advertisement=BLANK(), "Planning",
    EventStatus = "On going" && Advertisement<>BLANK() && Selling=BLANK(), "Advertisement",
    EventStatus = "On going" && Selling<>BLANK() && Closing=BLANK(), "Selling",
    EventStatus = "Closed" && Closing<>BLANK(), "Closing",
    ""
)

     
    Thank you.

     

    Regards,

    NickzNickz