5 Replies Latest reply: Feb 16, 2016 4:21 PM by Sunny Talwar RSS

    Dynamic Weeks

    Deepanshu Chamoli

      Kindly help me in the below logic. Any help is very useful

       

      I have created a variable in which Weeks Starts from Friday and End at Thursday. I stuck on the below logic

       

      Can you all please help me to implement the below logic in the Week:

      1. Only last four weeks is shown in Dimension (4- Latest Week, 3- Previous Week…).
      2. This pattern goes on even the Month changes. For E.g.: 29th Jan’16 – 4th Feb’16 is 4th Week & 22 Jan'16 - 28 Jan'16 is 3 Week.

       

      Let vMin = num(MakeDate(2014,02,01));

      Let vMax = num(TODAY());

      Load Date($(vMin) + RowNo() -1) as DATE,

           if(num(Weekday(Date($(vMin) + RowNo() -1)))>=4,

           Week(Date($(vMin) + RowNo() -1))+1,

           Week(Date($(vMin) + RowNo() -1))) as WEEK

      AutoGenerate 1

      while Date($(vMin) + RowNo() -1) < Date($(vMax));

       

      Thanks a lot in advance.

       

      Regards

      Deepanshu

        • Re: Dynamic Weeks
          Sunny Talwar

          May be this:

           

          Let vMin = num(MakeDate(2014,02,01));

          Let vMax = num(TODAY());

           

          LOAD *,

            If(TEMP <= 4, 5 - TEMP, 0) as WEEK_NUM_4M_TODAY;

          LOAD *,

            AutoNumber(Year(DATE)&WEEK) as TEMP;

          LOAD Date($(vMax) - IterNo() + 1) as DATE,

               if(num(Weekday(Date($(vMax) - IterNo() + 1)))>=4,

               Week(Date($(vMax) - IterNo() + 1))+1,

               Week(Date($(vMax) - IterNo() + 1))) as WEEK

          AutoGenerate 1

          while Date($(vMax) - IterNo() + 1) >= Date($(vMin));


          Capture.PNG

            • Re: Dynamic Weeks
              Deepanshu Chamoli

              Thanks a lot sir. stalwar1

               

              This is exactly what I want!!..

               

              Please guide me how to apply in master calendar. I want to make it dynamic.

               

              The difference between your logic and my is DATE($(vMinDate) +  RowNo() -1 ) as Tempdate. I am using this as my whole calendar.

               

              Please guide me how to implement the above logic in to this pattern.

               

              Minmaxtemp:

              Load

              Min(Open_Date) as MinDate,

              Max(Open_Date) as MaxDate

              Resident THR;

               

               

              Let vMinDate =  NUM(PEEK('MinDate',0 , 'Minmaxtemp'));

              Let vMaxDate =  NUM(PEEK('MaxDate',0 , 'Minmaxtemp'));

              Let vToday = $(vMaxDate);

               

               

              CalTemp:

                LOAD

                  DATE($(vMinDate) +  RowNo() -1 ) as Tempdate

              AutoGenerate

                $(vMaxDate) - $(vMinDate) +1;

               

               

              DROP TABLE Minmaxtemp;

               

               

              Master:

                Load

                  Tempdate as Open_Date,

                    if(num(Weekday(Tempdate))>=4,Week(Tempdate)+1, Week(Tempdate)) as Week

              Resident CalTemp

              Order by Tempdate desc;

               

               

              DROP TABLE CalTemp;

               

              Thanks a lot in advance

               

              Regards

              Deepanshu

                • Re: Dynamic Weeks
                  Sunny Talwar

                  I guess give this a shot:

                   

                  Minmaxtemp:

                  LOAD Min(Open_Date) as MinDate,

                            Max(Open_Date) as MaxDate

                  Resident THR;

                   

                  LET vMinDate =  Num(Peek('MinDate',0 , 'Minmaxtemp'));

                  LET vMaxDate =  Num(Peek('MaxDate',0 , 'Minmaxtemp'));

                  LET vToday = $(vMaxDate);

                   

                  CalTemp:

                  LOAD Date($(vMax) - IterNo() + 1) as Tempdate

                  AutoGenerate 1

                  While ($(vMax) - IterNo() + 1) >= $(vMin);

                   

                  DROP Table Minmaxtemp;


                  Master:

                  LOAD *,

                            If(Temp <= 4, 5 - TEMP, 0) as WEEK_NUM_4M_TODAY;

                  LOAD *,

                            AutoNumber(Year(Open_Date)&Week) as Temp;

                  LOAD Tempdate as Open_Date,

                            If(Num(WeekDay(Tempdate)) >= 4, Week(Tempdate)+1, Week(Tempdate)) as Week

                  Resident CalTemp

                  Order by Tempdate desc;

                   

                  DROP Table CalTemp;

                   

                  You should be able to do this for CalTemp also, since you are doing a Order by Tempdate desc in Master table load:


                  CalTemp: 

                  LOAD Date($(vMin) + IterNo() - 1) as Tempdate

                  AutoGenerate 1

                  While $(vMin) + IterNo() -1 <= $(vMax);

                   

                  Give them a shot and let me know if they work or not

                • Re: Dynamic Weeks
                  Deepanshu Chamoli

                  Thanks a lot Sunny for your help and time :-) Regards Deepanshu