Current 12 Weeks

building reports webintelligence
1

Hi,

I have a requirement where i need to display the Current 12 Week which includes the current Week and the Previous 11 Weeks. I need to display the Week Numbers as 1- 12.

I need some inputs as to how to dynamically change the Week Number for the current week and the previous ones. This has to be done in Webi 3.1

Any questions appreciated.

chetan.mudike :us: (BOB member since 2008-02-13)

2

First of all you have to tell us which data base you are using and format of your date.

Did you try using the Week Function?

Week(date input_date) will return a integer based on your calendar year and date distribution?

monty4u :us: (BOB member since 2006-12-12)

3

Hi…

I am fetching data from the oracle Database… and i have the Week Nbr from the Time DImension table, but it will give me the actual weel number. i was the current week to display as ‘12’ and the previous week as 11 and so one for the last 12 weeks.

chetan.mudike :us: (BOB member since 2008-02-13)

4

do you need a moving 12 weeks? if yes, you can use

date between current date-(12*7) and current date

cyg (BOB member since 2004-06-14)

5

hey CYG,

Thanks for your inputs…but my requirement is:

I need to show the current week as the week no.12 and the previous week as 11 and so on…this is a cross tab report…

Let me know if you need anything info.

chetan.mudike :us: (BOB member since 2008-02-13)

6

Hi
Create variables as below:

V_1stDayOfWeek=RelativeDate(LastDayOfWeek([Hiredate3]);-7)
V_Weeknums=If([Hiredate3] Between(RelativeDate([V_1stDayOfWeek];-(7*2));RelativeDate([V_1stDayOfWeek];-(7*1)));"12thWeek";
If([Hiredate3] Between(RelativeDate([V_1stDayOfWeek];-(7*3));RelativeDate([V_1stDayOfWeek];-(7*2)));"11thWeek";
If([Hiredate3] Between(RelativeDate([V_1stDayOfWeek];-(7*4));RelativeDate([V_1stDayOfWeek];-(7*3)));"10thWeek";
If([Hiredate3] Between(RelativeDate([V_1stDayOfWeek];-(7*5));RelativeDate([V_1stDayOfWeek];-(7*4)));"9thWeek";
If([Hiredate3] Between(RelativeDate([V_1stDayOfWeek];-(7*6));RelativeDate([V_1stDayOfWeek];-(7*5)));"8thWeek";
If([Hiredate3] Between(RelativeDate([V_1stDayOfWeek];-(7*7));RelativeDate([V_1stDayOfWeek];-(7*6)));"7thWeek";
If([Hiredate3] Between(RelativeDate([V_1stDayOfWeek];-(7*8));RelativeDate([V_1stDayOfWeek];-(7*7)));"6thWeek"
)

)
)
)
)
)
)

Rajubollas :india: (BOB member since 2009-09-04)

7

Hi Raju

Thanks for your inputs…

I havea column in my DB which gives me the last day of the week and the Data is designed in such a way that the EOW is defined by the last day of the week that is Friday. SO i guess i dont have to use the 1st function using Relative Date or should i use the Last day of the week?

chetan.mudike :us: (BOB member since 2008-02-13)

8

Welcome!

[quote:d90a018e81=“chetan.mudike”]
I havea column in my DB which gives me the last day of the week and the Data is designed in such a way that the EOW is defined by the last day of the week that is Friday.
[/quote]

To get 1st day of the week you need first function.

Create the variable and test it, if that is not required you can remove later right.

Rajubollas :india: (BOB member since 2009-09-04)

9

Hi Raju,

After some discussion with the business the requirement has bit changed.

For the Business the Start Date is Sat and the End Date is Friday.so how do we have to consider the calculation for this to get the Week.

chetan.mudike :us: (BOB member since 2008-02-13)

10

Just small modification is required:

V_LastDayOfWeek=RelativeDate(LastDayOfWeek([Hiredate3]);-8)

will give Friday

V_1stDayOfWeek=RelativeDate(LastDayOfWeek([Hiredate3]);-7)

Will give Saturday

Rajubollas :india: (BOB member since 2009-09-04)