General OBIEE

Get Involved. Join the Conversation.

Topic

    Karen Bredehoeft
    Filter for terminations in prior calendar week
    Topic posted September 21, 2017 by Karen BredehoeftBronze Trophy: 5,000+ Points, tagged Analyses, Core HR, OTBI, SQL 
    148 Views, 3 Comments
    Title:
    Filter for terminations in prior calendar week
    Summary:
    I'm creating a termination report and trying to show which terminations were in the calendar week (Sunday-Saturday) prior from the week when the report was run
    Content:

    Hello,

    I'm trying to create a report that shows which terminations were from the prior calendar week. So far I've only been able to create the below formula to show terms for 1 week prior to current date, but it's calculating the past 7 days, not calendar week. I've tried changing the formula to use CALENDAR WEEK but to no avail. 

    CASE  WHEN (("Worker"."Termination Date" IN (timestampadd(SQL_TSI_WEEK,-1,CURRENT_DATE))) OR ("Worker"."Termination Date" IN (CURRENT_DATE))) THEN 'Last Week' ELSE '' END

    I would like this to be a value in a column. So for example, if we ran the report today, Thursday September 21st, any employee whose termination date was between Sunday, Sept 10 and Saturday Sept 16 would have the value LAST WEEK, in the column. We would then be able to use that column as a table prompt to show terminations in the last week. We do not want it as a filter on the entire report, because we are using the same analysis criteria to also show terminations by mth and quarter. 

    I've attached a screenshot of what we're trying to get to. 

    Image:

    Comment

     

    • Nirmal

      Hi Karen,

      Try the below formula, it will give you the expected result.

      CASE WHEN "Worker"."Termination Date"  BETWEEN timestampadd(SQL_TSI_DAY,(-6-(DAYOFWEEK(current_date))),CURRENT_DATE)  AND timestampadd(SQL_TSI_DAY,-(DAYOFWEEK(current_date)),CURRENT_DATE) THEN 'Last Week' END

      Let us know if this resolves your issue.

      Thanks
      Nirmal

      http://www.dataterrain.com