Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Blank or Null Date Validation

I am trying to write basic if condition considering date field, there are chances of blank in date field. I am aiming to name blank fields based in if loop. Below is the condition I write but for each and every row I am getting DMT only

 

Lifecycle = IF(POC_SBSPN_LIFECYC_ACTUAL[Schd Dt]>=TODAY()-30, "30DayActive",IF(POC_SBSPN_LIFECYC_ACTUAL[Schd Dt]<TODAY()-90, "Dormant",IF(POC_SBSPN_LIFECYC_ACTUAL[Schd Dt]=BLANK(), "Never Active",IF(AND(POC_SBSPN_LIFECYC_ACTUAL[ Schd Dt]>=TODAY()-90,POC_SBSPN_LIFECYC_ACTUAL[Schd Dt]<TODAY()-30),"Inactive", "Others"))))
 
I try both BLANK(), ISBLANK option please help.
 
Thanks - Krishna
Need Help
  • DoubleJ's avatar
    DoubleJ
    6 years ago

    Hi

    I would suggest that you use the ISBLANK() check as first condition.
    Personally I prefer a SWITCH() to nested IFS(). The code could look like this:

    Lifecycle = 
SWITCH(TRUE(),
ISBLANK(POC_SBSPN_LIFECYC_ACTUAL[Schd Dt]),"Never Active",
POC_SBSPN_LIFECYC_ACTUAL[Schd Dt]>=TODAY()-30,"30DayActive",
POC_SBSPN_LIFECYC_ACTUAL[Schd Dt]>=TODAY()-90 && POC_SBSPN_LIFECYC_ACTUAL[Schd Dt] < TODAY()-30, "Inactive",
POC_SBSPN_LIFECYC_ACTUAL[Schd Dt]<TODAY()-90,"Dormant",
"Other")

     

    Hope this helps!
    JJ

3 Replies

  • DoubleJ's avatar
    DoubleJ
    Icon for Solution Supplier rankSolution Supplier
    6 years ago

    Hi

    I would suggest that you use the ISBLANK() check as first condition.
    Personally I prefer a SWITCH() to nested IFS(). The code could look like this:

    Lifecycle = 
SWITCH(TRUE(),
ISBLANK(POC_SBSPN_LIFECYC_ACTUAL[Schd Dt]),"Never Active",
POC_SBSPN_LIFECYC_ACTUAL[Schd Dt]>=TODAY()-30,"30DayActive",
POC_SBSPN_LIFECYC_ACTUAL[Schd Dt]>=TODAY()-90 && POC_SBSPN_LIFECYC_ACTUAL[Schd Dt] < TODAY()-30, "Inactive",
POC_SBSPN_LIFECYC_ACTUAL[Schd Dt]<TODAY()-90,"Dormant",
"Other")

     

    Hope this helps!
    JJ

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Yes, finally SWITCH is working well. I tested with other conditions as well. 

       

      On the other I created a new column to test Isblank having only isblank condition and that is also working but not with other conditions including. Not sure what makes the difference. 

       

      Thanks - Krishna.

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    ISBLANK(POC_SBSPN_LIFECYC_ACTUAL[Schd Dt])  should have work.  Can you share a sample file.