Switch with dates and text values/format

[jmetf150]

I am trying to write a SWITCH function that looks at a TEXT column ( [RS] ), and depending on the text value "OT", then compares between two other DATE columns ( [RDD] & [RCD] )to derive another TEXT column. 

Cond = 
switch(
   'R'[RS]="OT",
   'R'[RDD]<='R'[RCD], "PD",
   "LLT"
)

[jmetf150]

@Greg_Deckler to piggy back on this topic on IF/SWITCH, how would I do write the below formula if if I wanted to see orders that are "Late" (i.e. arriving after our need date) and if they are late, are they late in the past (i.e. PO del was last month)  or are they late in the future (ie Need date is next month but current PO Due date is even after that). 

The below is what I have know

LateStatus = if(ZMMAE[OT X 2 to Cur MRP Need]="Late" && if(ZMMAE[Del Date]>ZMMAE[Cur MRP Need], "Late in Past" && if(ZMMAE[Del Date]<ZMMAE[Cur MRP Need],"Late in the Future",""))

and I keep getting "Too few arguments were passed to the IF cunction.minimum argument is 2....".

[Greg_Deckler]

@jmetf150 Try:

LateStatus = 
  SWITCH( TRUE(),
    ZMMAE[OT X 2 to Cur MRP Need]="Late" && ZMMAE[Del Date]>ZMMAE[Cur MRP Need], "Late in Past",
    ZMMAE[OT X 2 to Cur MRP Need]="Late" && ZMMAE[Del Date]<ZMMAE[Cur MRP Need],"Late in the Future",
    ""
  )

[jmetf150]

That is working to only pull the "Late" orders but it is saying all of the late orders are "In THe Past".  I am fairly certain that is incorrect but the source data consists of over 7K lines so let me find one that is "Late in the Future" and see what it is pulling that way.

[jmetf150]

@Greg_Deckler can you explain the difference between both of your solutions and when you would use one over the other?

[Greg_Deckler]

@jmetf150 Well, you would use a "normal" SWITCH statement when you have different values you want to return for different values in a column for example. There's no complex logic required in terms of when you want to return which values. So, let's say you have a column that can contain Pickle, Banana or Cucumber and you want to return 1 for Pickle, 2 for Banana and 3 for Cucumber. This is the second example.

The 1st example, the SWITCH(TRUE() ) statement is used when you have complex criteria, so return 1 if Pickle and the Value is > 30 for example.

Also note that in this specific use case, you could have used an IF statement, 

IF( 'R'[RS]="OT" && 'R'[RDD]<='R'[RCD], "PD", "LLT")

[Greg_Deckler · Accepted solution]

@jmetf150 I think you want: 

Cond = 
switch(TRUE(),
   'R'[RS]="OT", IF('R'[RDD]<='R'[RCD], "PD", "LLT")
)


or

Cond = 
switch('R'[RS],
   "OT", IF('R'[RDD]<='R'[RCD], "PD", "LLT")
)

[jmetf150]

Deleted so not to confuse anyone

[jmetf150]

Ok....I figured it out...my inital formula was incorrect...I was using an "="OT"" sign when I should have been using a "<>""OT".....Thank you very much for the soltuion Greg!