Oracle Transactional Business Intelligence

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Filter on blank and future dates

Received Response
246
Views
2
Comments

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

Answers

  • Aaron Leggett
    Aaron Leggett Rank 5 - Community Champion

    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

  • SantoshKumarBhairi
    SantoshKumarBhairi Rank 6 - Analytics Lead

    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