Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Tried with hic solution and its working fine for 2017 and 2016 but having issue 2018 week1 (week1: 2018-1-1 to 2018-1-6 instead of 2018-1-1 to 2018-1-7). do you have any idea to resolve it. thanks
fyi, used below script
T_MinMax:
LOAD
Min(FieldValue('Date',RecNo())) as MinDate,
Max(FieldValue('Date',RecNo())) as MaxDate
AutoGenerate FieldValueCount('Date');
LET vMinDate = num(Peek('MinDate'));
LET vMaxDate = num(Peek('MaxDate'));
LET vToday = vMaxDate;
DROP Table T_MinMax;
Set vCal_FD = 0; // First Day of the week {0=Mon, 1=Tue, ... , 6=Sun}
Set vCal_BW = 0; // Broken Weeks allowed {0=No, 1=Yes}
Set vCal_RD = 4; // Reference day = This day in Jan defines week one {1..7}
MasterCalendar:
LOAD CalDate as Date,
Day(CalDate) as Day,
Month(CalDate) as Month,
WeekDay(CalDate) as WeekDay,
'Q' & Ceil(Month(CalDate)/3) as Quarter,
Date(MonthStart(CalDate), 'MM-YYYY') as MonthYear,
Div( CalDate - WeekStart( WeekYearRefDate, 0, $(vCal_FD) ) + 7, 7 ) as Week,
Year( WeekYearRefDate ) as Year;
Load *,
Date( YearStart( If( $(vCal_BW), CalDate, WeekRefDate )) + $(vCal_RD) - 1) as WeekYearRefDate ;
Load *,
Date( WeekStart( CalDate, 1, $(vCal_FD) ) - $(vCal_RD) ) as WeekRefDate ;
LOAD Date($(vMinDate) + IterNo()) as CalDate
AutoGenerate 1
While $(vMinDate) + IterNo() <= $(vMaxDate);
reference used
https://community.qlik.com/blogs/qlikviewdesignblog/2014/01/27/week-numbers#comment-64485
expected output
2018 Week1 (from 1st Jan 2018 to 7th Jan 2018)
it should work all the year dynamically with iso week
Thanks,
Deva
I have used the same script and it looks good.
Regards,
Nimesh
Hi,
can you share the screen. the above mentioned script shows till 6th jan 2018 for ISO week 1 (suppose it should show till 7th Jan 2018)
Thanks,
Deva
Hi,
Figure it out. seems my QVD dosn't have 7th Jan data. after reloading the missing data and issue resolved. Tks
Exactly. I was about to ask your file to check if you're getting Date from fact table if so, you will not have 7th.
Rgds,
Nimesh
what is in your Date field? it seems that your last generated date is 2018-01-06