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: 
devarasu07
Master II
Master II

ISO Calendar issue

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

Capture.JPG

expected output

Week Numbers for 2018

2018 Week1 (from 1st Jan 2018 to 7th Jan 2018)

it should work all the year dynamically with iso week

Thanks,

Deva

5 Replies
lnimeshkrishnan
Contributor II
Contributor II

I have used the same script and it looks good.

QC_ISO_WKI.PNG

Regards,

Nimesh

devarasu07
Master II
Master II
Author

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

devarasu07
Master II
Master II
Author

Hi,

Figure it out. seems my QVD dosn't have 7th Jan data. after reloading the missing data and issue resolved. Tks

lnimeshkrishnan
Contributor II
Contributor II

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

t_chetirbok
Creator III
Creator III

what is in your Date field? it seems that your last generated date is 2018-01-06