Loadorder of WeekDiff field in MasterCalendar

[Frank_Hartmann]

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

[sunny_talwar]

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

[sunny_talwar]

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

[Frank_Hartmann]

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

[sunny_talwar · Accepted solution]

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;

[Frank_Hartmann]

Thank you again