Forum Discussion

NickzNickz's avatar
NickzNickz
Helper 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:

BeaBF's avatar
BeaBF
Super User
2 years ago

NickzNickz Hi! Try with:

NewOutput =
IF (
Table1[Status] = "Planning"
&& NOT ISBLANK ( Table1[Date1] )
&& ISBLANK ( Table1[Date2] )
&& ISBLANK ( Table1[Date3] )
&& ISBLANK ( Table1[Date4] ),
"Planning",
IF (
Table1[Status] = "On going"
&& NOT ISBLANK ( Table1[Date1] )
&& NOT ISBLANK ( Table1[Date2] )
&& ISBLANK ( Table1[Date3] )
&& ISBLANK ( Table1[Date4] ),
"Advertisement",
IF (
Table1[Status] = "On going"
&& NOT ISBLANK ( Table1[Date1] )
&& NOT ISBLANK ( Table1[Date2] )
&& NOT ISBLANK ( Table1[Date3] )
&& ISBLANK ( Table1[Date4] ),
"Selling",
IF (
Table1[Status] = "Closed"
&& NOT ISBLANK ( Table1[Date1] )
&& NOT ISBLANK ( Table1[Date2] )
&& NOT ISBLANK ( Table1[Date3] )
&& NOT ISBLANK ( Table1[Date4] ),
"Closing",
BLANK ()
)
)
)
)

 

BBF

NickzNickz's avatar
NickzNickz
Helper IV
2 years ago

Hi BeaBF ,

 

I have tested and the result not matching.

I also tested below formula :

Output Column = 
IF(
    f_event_list[Status] = "Planning" && NOT(ISBLANK(f_event_list[Planning Date])), "Planning",
    IF(
        f_event_list[Status] = "On going" && NOT(ISBLANK(f_event_list[Planning Date])) && NOT(ISBLANK(f_event_list[Advertisment Date])) && (ISBLANK(f_event_list[Date Selling Start])), "Advertisement",
        IF(
            f_event_list[Status] = "On going" && NOT(ISBLANK(f_event_list[Planning Date])) && NOT(ISBLANK(f_event_list[Advertisment Date])) && NOT(ISBLANK(f_event_list[Date Selling Start])), "Selling",
             IF(
                f_event_list[Status] = "Closed" && NOT(ISBLANK(f_event_list[Planning Date])) && NOT(ISBLANK(f_event_list[Advertisment Date])) && NOT(ISBLANK(f_event_list[Original Closing Date])) && NOT(ISBLANK(f_event_list[Original Closing Date])), "Closing",
                BLANK()
            )
        )
    )
)

and the result as below. I still have an issue with On going status .... 

Regards,

NickzNickz