Create Weeks

[ch_riadh]

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.

[#2]

Hi,

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

[ch_riadh]

Please can u explan with more details ?

( Step by Step )

Thanks

[kaushiknsolanki]

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

[#5]

Hi,

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

[thomduvi83]

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

[ch_riadh]

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.

[Gysbert_Wassenaar]

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;