topic Re: ISO Calendar issue in QlikView

ISO Calendar issue

devarasu07 — Wed, 25 Nov 2020 16:16:04 GMT

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

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

Re: ISO Calendar issue

lnimeshkrishnan — Mon, 08 Jan 2018 07:02:49 GMT

I have used the same script and it looks good.

Regards,

Nimesh

Re: ISO Calendar issue

devarasu07 — Mon, 08 Jan 2018 07:20:41 GMT

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

Re: ISO Calendar issue

devarasu07 — Mon, 08 Jan 2018 07:35:38 GMT

Hi,

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

Re: ISO Calendar issue

lnimeshkrishnan — Mon, 08 Jan 2018 07:37:41 GMT

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

Re: ISO Calendar issue

t_chetirbok — Mon, 08 Jan 2018 07:54:21 GMT

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