5 Replies Latest reply on Aug 3, 2015 7:09 AM by asim cholas

# OBIEE: Sum only if week is already finished

Hi, OBIEE experts.

I have a report requirement where in i will only show/sum the measure if week is already complete, how can i do this in OBIEE?

Another thing is i have to get the number of week/s that is already finished because i will use it to divide the sum of figures (week run rate).

sample

WEEK1 WEEK2 WEEK3 WEEK4 MONTH 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

because week 5 is not yet complete, no data is shown.

here's my modified calendar:

MONTH_SDESCWEEK_NUMBER_IN_MONTHDATE_VALUEDAY_OF_WEEK_SDESC
JUL17/1/2015WED
7/2/2015THU
7/3/2015FRI
7/4/2015SAT
7/5/2015SUN
27/6/2015MON
7/7/2015TUE
7/8/2015WED
7/9/2015THU
7/10/2015FRI
7/11/2015SAT
7/12/2015SUN
37/13/2015MON
7/14/2015TUE
7/15/2015WED
7/16/2015THU
7/17/2015FRI
7/18/2015SAT
7/19/2015SUN
47/20/2015MON
7/21/2015TUE
7/22/2015WED
7/23/2015THU
7/24/2015FRI
7/25/2015SAT
7/26/2015SUN
57/27/2015MON
7/28/2015TUE
7/29/2015WED
7/30/2015THU
7/31/2015FRI
• ###### 1. Re: OBIEE: Sum only if week is already finished

Hope you have table W_DAY_D then try to utilize field W_CURRENT_CAL_WEEK_CODE

or else go for variables that would flag current week.

• ###### 2. Re: OBIEE: Sum only if week is already finished

Hi, Sir Srini.

May I know what does W_DAY_D contains?

• ###### 3. Re: OBIEE: Sum only if week is already finished

W_DAY_D is the date dimension table and contains date related information in it. Please find below the a screenshot of the table.

• ###### 4. Re: OBIEE: Sum only if week is already finished

Just so we're clear to the OP:

W_DAY_D is one way of doing date dimension and comes from the Oracle BI Applications product line.

It is not the ultimate answer to all questions but one way of midellimg a date dimension.

• ###### 5. Re: OBIEE: Sum only if week is already finished

Hi,

Below formula will provide you week ending date (here i am considering friday as weekending date. You can modify if you need another day as weekending date)