Forum Discussion

jmetf150's avatar
jmetf150
Icon for Helper I rankHelper I
2 years ago
Solved

Switch with dates and text values/format

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...
  • Greg_Deckler's avatar
    Greg_Deckler
    2 years ago

    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's avatar
jmetf150
Icon for Helper I rankHelper I
2 years ago

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's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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's avatar
      jmetf150
      Icon for Helper I rankHelper I
      2 years ago

      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.