Oracle Transactional Business Intelligence

Products Banner

Formula to capture last 7 days of data

189
Views
7
Comments

Summary

Formula to capture last 7 days of data

Content

Hello,

 

We are looking to capture requisition in specific statuses (Approved, To Be Approved, Draft, Sourcing and Filled) for the last seven calendar days.  Look for assistance in creating a formula to capture that timeframe so I can set up a weekly agent to run the report.

Comments

  • rajArun
    rajArun ✭✭✭✭

    You may use the below variable: 

    "date variable" > TIMESTAMPADD(SQL_TSI_DAY, -7, CURRENT_DATE) 

    Thanks to Manoj who had posted the above formula in another post. Here is the link to the original post

     

    Thanks,

    Arun

     

  • Hello!

    Would you by chance know how to structure this formula to bring back candidates who have been rejected in the last seven days?

     

    Thanks!

    Joy

  • Hi Joy,

    You can use the below for formula for  Rejected Candidates for the last 7 days.

     

    "Application Current CSW"."Application Current CSW Status" IS IN Candidate Reject - Not Applicant;Candidate Reject — No Business Need;Candidate Rejected;Candidate Withdrew
    AND
    "Application Current CSW Start Date"."Application Current CSW Start Date"  is Greater Than TIMESTAMPADD(SQL_TSI_DAY, -6,"Special Objects"."Current Date")

     

    Regards,

    Ramesh

  • Hi Ramesh, thank you so much! This ended up working for my needs.

    Joy

  • Thanks Joy. Great to know that.

     

     

    Regards,

    Ramesh

     

     

  • I am looking at incomplete submissions and desire to see those that are in the last seven days.

    I am using the field Submission Created Date in the Criteria as a filter.  I can enter a value of 3/8/2019 and all works fine.  Using your notes above, I'm using 'Add More Options" > SQL Expressions and entered:   "Submission Dates"."Submission Created Date" >= TIMESTAMPADD(SQL_TSI_DAY, -7, CURRENT_DATE)

    After running the report, an error is presented:

    Error getting drill information: SELECT "Submission Dates"."Submission Created Date" saw_0, "Submission General Info"."Submission Is Completed" saw_1, "Submission General Info"."Career Site Last Page Reached Name (BL)" saw_2, "Submission General Info"."Career Site Last Page Reached Sequence" saw_3, "Candidate Identification"."Name" saw_4, "Candidate Identification"."Email" saw_5, "Candidate Identification"."Candidate Identifier" saw_6, "Requisition Identification"."Req. Identifier" saw_7, "Submission Source"."Submission Source (BL)" saw_8, "Requisition Identification"."Title (BL)" saw_9 FROM "Recruiting" WHERE ("Submission General Info"."Submission Originator" = 'Candidate') AND ("Submission General Info"."Career Site Name (BL)" IN ('Corporate', 'Corporate_mobile')) AND (("Submission Dates"."Submission Created Date" IN ("Submission Dates"."Submission Created Date" >= TIMESTAMPADD(SQL_TSI_DAY, -7, CURRENT_DATE)))) AND ("Requisition Organization"."Organization Level1 Name" = 'Aerospace') AND ("Requisition Primary Location"."Location Level1 Name" = 'United States') AND ("Submission General Info"."Submission Is Completed" = 'No')

     

    Error Codes: YQCO4T56:OPR4ONWY:U9IM8TAC:U9IM8TAC:U9IM8TAC:U9IM8TAC:OI2DL65P
    Odbc driver returned an error (SQLExecDirectW).
    State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. (HY000)
    State: HY000. Code: 43113. [nQSError: 43113] Message returned from OBIS. (HY000)
    State: HY000. Code: 27002. [nQSError: 27002] Near <>=>: Syntax error (HY000)
    State: HY000. Code: 26012. [nQSError: 26012] . (HY000)

     

    Any help you could provide?  Thanks, Rhea

  • rajArun
    rajArun ✭✭✭✭

    Hi Rhea,

    You have to select "Is Greater Than" condition and then add SQL Expression as TIMESTAMPADD(SQL_TSI_DAY, -7, CURRENT_DATE)

    See the attached image.

    Hope this fixes the issue.

    Regards,
    Arun

    BI answers Date Filter.jpg