Oracle Transactional Business Intelligence

Products Banner

Formula Help

46
Views
3
Comments

Summary

Need help developing formula for date range

Content

Hello All,

I am looking for help developing a formula that will help me identify if the date in a column is within 45 days or not within 45 days from the date the report was run.  Any ideas where to start?

 

Comments

  • Hi Nicole,

    I have exactly the same thing in one of my reports. This tells me who all are going to join within 45 days from today. Below is the formula, which will work perfectly for you.

    CASE  WHEN TRUNCATE(TIMESTAMPDIFF(SQL_TSI_DAY,CURRENT_DATE, "Submission Dates"."Offer Actual Start Date"),0) <= 45 THEN '45 days or Less'  WHEN TRUNCATE(TIMESTAMPDIFF(SQL_TSI_DAY,CURRENT_DATE, "Submission Dates"."Offer Actual Start Date"),0) > 45 THEN '> 45 days' END

    So you just need to replace "Submission Dates"."Offer Actual Start Date" in this formula with your date field. Also, please note this is a future date, so If you want to calculate backwards with past date then you'll have to swap the place of CURRENT_DATE & your date field.

    Let me know if run into some issues, I can help you on this.

    Thanks,

    Sachin

  • Thank you this is perfect!  Does this formula work on a UDF field that is used to capture a date?  I was able to get the formula to work on the Offer Actual Start Date column but I am not able to get it to work on UDF field we use to capture dates.
  • Hi Nicole,

    You can use the CAST function to convert the UDF into Date

    CAST( UDF AS DATE)

    Regards,

    Kiran