topic Re: Loadorder of WeekDiff field in MasterCalendar in QlikView

Loadorder of WeekDiff field in MasterCalendar

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

Hi Community,

Is anyone able to tell me why the loadorder of my WeekDiff field in MasterCalendar is not correct and how it should be changed in order to get it work?

LET vDateMin = Num(MakeDate(2017,1,1));
LET vDateMax = Num(MakeDate(2019,1,1));
LET vDateToday = Num(Today());


TempCalendar:  
LOAD 
$(vDateMin) + RowNo() - 1 AS DateNumber,  
Date($(vDateMin) + RowNo() - 1) AS TempDate
AUTOGENERATE 1  
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);  


MasterCalendar:  
LOAD 
(year(TempDate)*52 + week(TempDate)) - (year($(vDateMin))*52 + week($(vDateMin))) as CalenderNumber,
(year(TempDate)*52 + week(TempDate)) - (year($(vDateToday))*52 + week($(vDateToday))) as WeekDiff,
Num(Week(TempDate),'00') & '-' & Year(TempDate) as CalendarWeekAndYear
RESIDENT TempCalendar ORDER BY TempDate  ASC;

Any help is highly appreciated!

Best regards

Frank

Re: Loadorder of WeekDiff field in MasterCalendar

sunny_talwar — Wed, 17 Jan 2018 18:57:40 GMT

Something else is wrong... look at the TempDate for 52-2017... how can 01.01.2017 be 52-2017

Re: Loadorder of WeekDiff field in MasterCalendar

sunny_talwar — Wed, 17 Jan 2018 19:01:19 GMT

To me it appears to be an issue with the Week function

Re: Loadorder of WeekDiff field in MasterCalendar

Frank_Hartmann — Wed, 17 Jan 2018 19:04:45 GMT

If I use

LET vDateMin = Num(MakeDate(2017,1,2));

then it works.

But with

LET vDateMin = Num(MakeDate(2017,1,1));

its giving the wrong sortorder.

very strange

Re: Loadorder of WeekDiff field in MasterCalendar

sunny_talwar — Wed, 17 Jan 2018 19:05:28 GMT

Try this

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='#.##0,00 €;-#.##0,00 €';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD.MM.YYYY';

SET TimestampFormat='DD.MM.YYYY hh:mm:ss.fff';

SET MonthNames='Jan;Feb;Mrz;Apr;Mai;Jun;Jul;Aug;Sep;Okt;Nov;Dez';

SET DayNames='Mo;Di;Mi;Do;Fr;Sa;So';

//LET vDateMax = Floor(YearEnd(AddMonths(Today(), 1)));

LET vDateMin = Num(MakeDate(2017,1,1));

LET vDateMax = Num(MakeDate(2019,1,1));

LET vDateToday = Num(Today());

TempCalendar:

LOAD

$(vDateMin) + RowNo() - 1 AS DateNumber,

Date($(vDateMin) + RowNo() - 1) AS TempDate

AUTOGENERATE 1

WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

MasterCalendar:

LOAD

(year(WeekName(TempDate))*52 + week(WeekName(TempDate))) - (year($(vDateMin))*52 + week($(vDateMin))) as CalenderNumber,

(year(WeekName(TempDate))*52 + week(WeekName(TempDate))) - (year($(vDateToday))*52 + week($(vDateToday))) as WeekDiff,

Num(Week(WeekName(TempDate)),'00') & '-' & Year(WeekName(TempDate)) as CalendarWeekAndYear

RESIDENT TempCalendar ORDER BY TempDate ASC;

Re: Loadorder of WeekDiff field in MasterCalendar

Frank_Hartmann — Wed, 17 Jan 2018 19:09:51 GMT

Thank you again