BusinessObjects Board

Create variable

building reports webintelligence
#1

HI,

I need help with custome functions in webi, please guide on creating the following variable:
LastFullWeek – based on the current date, it takes the last full week (Sun-Sat) so we do not have to hard code our dates.

Thanks,
Neha.k

neha.k (BOB member since 2012-02-12)

custom dates
custom dates
#2

If your DB is Oracle, then this might help guide you along

select next_day(trunc(sysdate), 'sunday') - 14, next_day(trunc(sysdate), 'saturday') - 7 from dual;

Theres a nice sticky on Relative Date Functions found here which is where I found the above code

mwinchel :us: (BOB member since 2011-12-13)

#3

i want to create variable
can i write this as formula

neha.k (BOB member since 2012-02-12)

#4

Untested fully but for today’s date, this seems to be working.

Create two variables:

lstWeekStart:

=If(DayNumberOfWeek(CurrentDate())=1) Then (RelativeDate(CurrentDate();-8)) Else
If(DayNumberOfWeek(CurrentDate())=2) Then (RelativeDate(CurrentDate();-9)) Else
If(DayNumberOfWeek(CurrentDate())=3) Then (RelativeDate(CurrentDate();-10)) Else
If(DayNumberOfWeek(CurrentDate())=4) Then (RelativeDate(CurrentDate();-11)) Else
If(DayNumberOfWeek(CurrentDate())=5) Then (RelativeDate(CurrentDate();-12)) Else
If(DayNumberOfWeek(CurrentDate())=6) Then (RelativeDate(CurrentDate();-13)) Else
(RelativeDate(CurrentDate();-7))

lstWeekEnd:

=If(DayNumberOfWeek(CurrentDate())=1) Then (RelativeDate(CurrentDate();-2)) Else If(DayNumberOfWeek(CurrentDate())=2) Then (RelativeDate(CurrentDate();-3)) Else If(DayNumberOfWeek(CurrentDate())=3) Then (RelativeDate(CurrentDate();-4)) Else If(DayNumberOfWeek(CurrentDate())=4) Then (RelativeDate(CurrentDate();-5)) Else If(DayNumberOfWeek(CurrentDate())=5) Then (RelativeDate(CurrentDate();-6)) Else If(DayNumberOfWeek(CurrentDate())=6) Then (RelativeDate(CurrentDate();-7)) Else (RelativeDate(CurrentDate();-1))

See if this does what youre looking for

mwinchel :us: (BOB member since 2011-12-13)

#5

Hi,

You can simplify the two above formulas to these:

Week Start:

=RelativeDate(CurrentDate();-DayNumberOfWeek(CurrentDate())-7)

Week End:

=RelativeDate(CurrentDate();-DayNumberOfWeek(CurrentDate())-1)

Marek Chladny :slovakia: (BOB member since 2003-11-27)

#6

You can refer the below response

https://bobj-board.org/t/201030

TBTS (BOB member since 2012-09-18)