2 Replies Latest reply: Jul 7, 2016 2:54 AM by Chanty 4u RSS

    Customized month start and end date

    Supriya R

      Hi all,

       

      I want to display month start and end date according to campaign specific.

       

      Here campaign starts from tuesday to sunday in a month, we wont take continuation of next month to current month date.but continuation of previous month is taken as current month start.

      Example: If you take july calender, my month start  should be: 27-06-2016 month end : 31-07-2016

                        If you take june calender, my month start should be : 30-05-2016 month end : 26-06-2016

       

       

      Note: set your calender to start from monday.

       

      How could i do this please help me on this.

        • Re: Customized month start and end date
          Pravesh Dukhi

          Try tweaking the following script to fit your requirement:

          1. QuartersMap: 
          2. MAPPING LOAD  
          3. rowno() as Month, 
          4. 'Q' & Ceil (rowno()/3) as Quarter 
          5. AUTOGENERATE (12); 
          6.  
          7. Temp: 
          8. Load 
          9.                min(OrderDate) as minDate, 
          10.                max(OrderDate) as maxDate 
          11. Resident Orders; 
          12.  
          13. Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 
          14. Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 
          15. DROP Table Temp; 
          16.  
          17. TempCalendar: 
          18. LOAD 
          19.                $(varMinDate) + Iterno()-1 As Num, 
          20.                Date($(varMinDate) + IterNo() - 1) as TempDate 
          21.                AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 
          22.  
          23. MasterCalendar: 
          24. Load 
          25.                TempDate AS OrderDate, 
          26.                week(TempDate) As Week, 
          27.                Year(TempDate) As Year, 
          28.                Month(TempDate) As Month, 
          29.                Day(TempDate) As Day, 
          30.                YeartoDate(TempDate)*-1 as CurYTDFlag, 
          31.                YeartoDate(TempDate,-1)*-1 as LastYTDFlag, 
          32.                inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 
          33.                date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, 
          34.                ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 
          35.                Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 
          36.                WeekDay(TempDate) as WeekDay 
          37. Resident TempCalendar 
          38. Order By TempDate ASC; 
          39. Drop Table TempCalendar; 
          • Re: Customized month start and end date
            Chanty 4u

            chk dis

            LET varMinDate = Num(Peek('OrderDate', 0, 'Orders'));

            LET varMaxDate = Num(Peek('OrderDate', -1, 'Orders'));

            LET vToday = Num(today());

             

             

            //*************** Temporary Calendar ***************

            TempCalendar:

            LOAD

              $(varMinDate)+IterNo()-1 AS Num,

              Date($(varMinDate)+IterNo()-1) AS TempDate

            AUTOGENERATE 1 WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate);

             

             

            //*************** Master Calendar ***************

            MasterCalendar:

            LOAD TempDate AS OrderDate,

                 week(TempDate) AS Week,

                 year(TempDate) AS Year,

                 month(TempDate) AS Month,

                 day(TempDate) AS Day,

                 weekday(TempDate) AS WeekDay,

                 applymap('Quarters_Map', num(month(TempDate)), null()) AS Quarter,

                 date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,

                 week(TempDate)&'-'&Year(TempDate) AS WeekYear,

                 Year2Date(TempDate, 0, 1, $(vToday))*-1 AS CurYTDFlag,

              Year2Date(TempDate,-1, 1, $(vToday))*-1 AS LastYTDFlag

            RESIDENT TempCalendar 

            ORDER BY TempDate Asc;