Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
berryandcherry6
Creator II
Creator II

Customized month start and end date

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.

2 Replies
Not applicable

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; 
Chanty4u
MVP
MVP

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;