Discussion Board for collaboration on QlikView Scripting.
Hello Qlik experts,
I have a Date field from which I have created a MasterCalendar table.
However I need to create two fields which I don't know how to:
Here is an example of week numbers that should be assigned based on real dates:
Week 1: 01/01/2018
Week 2: 08/01/2018
Week 3: 15/01/2018
Week 4: 22/01/2018
Week 5: 29/01/2018
Week 1: 05/02/2017
Week 2: 12/02/2017
Week 3: 19/02/2017
Week 4: 26/02/2017
Week 1: 05/03/2017
Week 2: 12/03/2017
Week 3: 19/03/2017
Week 4: 26/03/2017
Each date above is a Monday of each week inside each month.
Anyone know how I can create something like this please?
Please add this to the load of your MasterCalendar.
'Week '& Ceil(Day(YourDateField)/7) as WeekNumberByMonth,
If(WeekDay(YourDateField) = 'Mon',YourDateField) as WeekDate
I hope it helps,
Please let me know if this helped to close this thread,
I think, since they have created a master calendar, the week dates should be created from the master calendar MASTER_DATE, not their date field.
Try this in your master calendar.
The field what you need is NumWeekInMonthGen
LET DateStart = MakeDate(2015,1); // Upload start date
if(Peek(MonthYearGen)=MonthYearGen and Peek([Year-WeekGen])=[Year-WeekGen], Peek([NumWeekInMonthGen]),
if(Peek(MonthYearGen)=MonthYearGen and Peek([Year-WeekGen])<>[Year-WeekGen],Peek([NumWeekInMonthGen])+1,1))
ID_DATE as 'Date',
Day(ID_DATE) as 'Day',
Year(ID_DATE) as 'Year',
Month(ID_DATE) as 'Month',
num(Month(ID_DATE)) as 'MonthNum',
WeekDay(ID_DATE) as 'Week Day',
Year(ID_DATE)&num(Month(ID_DATE),'00') as 'MonthYearGen',
WeekName(ID_DATE) as 'Year-WeekGen',
NetWorkDays(ID_DATE,ID_DATE) as 'Work day';
load date('$(DateStart)' + IterNo() -1) as ID_DATE