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

    Create Weeks

    Riadh Chaouch

      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
            Kaushik Solanki

            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
                Riadh Chaouch

                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

                    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;

                • Re: Create Weeks
                  Thomas Duvivier

                  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