topic Re: Create Weeks in Water Cooler

Create Weeks

ch_riadh — Thu, 22 Aug 2013 06:31:12 GMT

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

Thu, 22 Aug 2013 06:38:41 GMT

Hi,

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

Re: Create Weeks

ch_riadh — Thu, 22 Aug 2013 07:39:03 GMT

Please can u explan with more details ?

( Step by Step )

Thanks

Re: Create Weeks

kaushiknsolanki — Thu, 22 Aug 2013 08:12:16 GMT

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

Thu, 22 Aug 2013 08:24:19 GMT

Hi,

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

Re: Create Weeks

thomduvi83 — Thu, 22 Aug 2013 08:26:53 GMT

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

Re: Create Weeks

ch_riadh — Thu, 22 Aug 2013 12:31:16 GMT

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

Gysbert_Wassenaar — Thu, 22 Aug 2013 12:44:18 GMT

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

MyTable:

Load

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;