7 Replies Latest reply: Aug 22, 2013 8:44 AM by Gysbert Wassenaar

# Create Weeks

My Customer have 48 Weeks by Year and 4 Weeks by Month :

Week 1 : from 1 to 7,

Week 2 : from 8 to 15,

Week 3 : from 16 to 22,

Week 4 : from 23 to end of Month.

Please how can I get the data for this Week till Today ?

For example to day = 21/08/2013

So, week today = from 16/08/2013 to 21/08/2013.

Thanks.

• ###### Re: Create Weeks

Hi,

You can use WeekStart and WeekEnd functions to get the range of from and to dates.

• ###### Re: Create Weeks

Please can u explan with more details ?

( Step by Step )

Thanks

• ###### Re: Create Weeks

Hi,

I am sorry, I missunderstand you. Kaushik has given best example for you.

• ###### Re: Create Weeks

Hi,

You need to create a logic to create a week for this scenario.

The logic is, you create an if statement where you check the date and then assign a number.

Say for example.

If(Day(Date) >=1 and Day(Date) <=7,1,

If(Day(Date) >=8 and Day(Date) <=15,2,

If(Day(Date) >=16 and Day(Date) <=22,3,4))) as WeekNumber.

Now this will give you 1 to 4 number for each of the Month.

Now you can write another logic.

If(num(Month(Date)) =1,WeekNumber,

If(num(Month(Date)) =2,WeekNumber+1,

If(num(Month(Date)) =3,WeekNumber+2,.... and so on.

First Try to implement this logic by yourself, if not then will send you the example. But I want you to try by yourself and learn

Regards,

Kaushik Solanki

• ###### Re: Create Weeks

Hi Kaushik,

when i right in the editor :

If(Day(DW.ISSUEDT) >=1 and Day(DW.ISSUEDT) <=7,1,

If(Day(DW.ISSUEDT) >=8 and Day(DW.ISSUEDT) <=15,2,

If(Day(DW.ISSUEDT) >=16 and Day(DW.ISSUEDT) <=22,3,4))) as WeekNumber,

its not working

error.

• ###### Re: Create Weeks

The best solution is to create a field named WeekNumber in the script. For that you can use the expression Kaushik posted:

MyTable:

MyField1,

MyField2,

...etc,

If(Day(DW.ISSUEDT) >=1 and Day(DW.ISSUEDT) <=7,1,

If(Day(DW.ISSUEDT) >=8 and Day(DW.ISSUEDT) <=15,2,

If(Day(DW.ISSUEDT) >=16 and Day(DW.ISSUEDT) <=22,3,4))) as WeekNumber,

FROM MySource;

• ###### Re: Create Weeks

Create the field for the week number (i suppose it's already done) :

if(Day(YourDate)<=7,1,if(Day(YourDate)<=14,2,if(Day(YourDate)<=21,3,4) as Week

If you don't have any future data in your model, only data till today, you can use this set analysis to have your "week -today" values :

{<Year = {\$(=max({1}Year)}, Month = {\$(=max({1}Month)}, Week = {\$(=max({1}Week))}>}

if you have also future data :

{<Year = {\$(=max({1}Year)}, Month = {\$(=max({1}Month)}, Week = {\$(=max({1}Week))}, Date = {"<=\$(=today())"}>}

Creating a unique field for each distinct weeks in your datamodel by concatenating Year&Month&Week will ease these expression and could be useful to find directly the last week. A flag for past or future data could also avoid date format issue in the set analysis with "today()" function.

Hope it helps,

Thomas