General OBIEE

Get Involved. Join the Conversation.

Topic

    Stephanie Gott
    Filter on blank and future datesAnswered
    Topic posted September 10, 2019 by Stephanie GottSilver Medal: 2,000+ Points, tagged Analyses, OBI Answers, OBIEE Answers, OTBI, SQL 
    36 Views, 2 Comments
    Title:
    Filter on blank and future dates
    Summary:
    Create a filter using SQL expression to show just the null and future dated values
    Content:

    Hello, 

    I have a need to see only the null values and future dates. I'm strugglin to get the syntax for my SQL expression in the filter. I'm using "Is greater than OR null. See screenshots. Any suggestions? If SQL expression is not the right way to go, I'm open to other options. 

    Thanks,

    Stephanie

    Best Comment

    Santosh Kumar Bhairi

    Hi Stephanie, Can you please try?

    1) create two conditions -> (a) Due Date is greater than current_date (b) Due Date is null

    2) click on AND and change this to OR

     

    Comment

     

    • Aaron Leggett

      Hey Stephanie, 

      Try this: 

      (CASE WHEN IFNULL("Folder"."Due Date", CURRENT_DATE +1) > CURRENT_DATE THEN 'Y' ELSE 'N' END) = 'Y' 

      Just change the "Folder"."Due Date" to your Due Date column. 

      Essentially we're replacing the NULL values with the current date + 1, so they will always be greater than the current date. Should hopefully work! 

      Thanks

      Aaron

    • Santosh Kumar Bhairi

      Hi Stephanie, Can you please try?

      1) create two conditions -> (a) Due Date is greater than current_date (b) Due Date is null

      2) click on AND and change this to OR