MakeWeekDate is not working

[peisenrot]

Hello,

I have the formular 

MakeWeekDate(left(DataVersion,4),right(DataVersion,2)) as DataVersionWeek,

the DataVersion is given as

2022_cw51

2023_cw01

but the makeweekDate is everytime 1/2/2023

What is wrong here? I tried with MakeWeekDate(2022,53) which is also showing 1/2/2023.

best regards

Pia

[marcus_sommer]

The week/date interpretation depends on your settings by the interpretation-variables "ReferenceDay" and "BrokenWeeks". The help provides more information how they work, which settings are possible and a few examples.

[peisenrot]

hello, I expect you mean that infroamtion

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;-$#,##0.00';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-US';
SET CreateSearchIndexOnReload=0;
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';
SET NumericalAbbreviation='3:k;6:M;9:G;12:T;15:P;18:E;21:Z;24:Y;-3:m;-6:μ;-9:n;-12:p;-15:f;-18:a;-21:z;-24:y';

[marcus_sommer]

Yes - this variables were meant:

SET BrokenWeeks=1;
SET ReferenceDay=0;

In regard resolving the week() matching I could change the settings like our requirements. Therefore I assume that makeweekdate() is also effected from this settings - a bit trial & error should quickly show if it reacts and if you could set them like needed.

[N30fyte]

Very basic load script:

calctable:

Load *,
MakeWeekDate((left(text(DataVersion),4)),(right(text(DataVersion),2))) as myDataVersionWeek
;

dabletable:

LOAD * inline [

DataVersion

2022_cw51

2023_cw01

];

works for me.

It gives  11/12/2022 for  2022_cw51

and

1/1/2023 for 2023_cw01

based on my local date and calendar settings.

(Sunday 11 December was the first day of week 51 of 2022, and Sunday 1 January was the first day of week 1 of 2023). 

Works similarly as achart expression and a variable.

Hope it helps...

[peisenrot]

where can i find your requirements ?

[N30fyte]

I don't understand what you mean by my 'requirements'. 

In my load script I have

SET DateFormat='D/M/YYYY';

...
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;

But the precise values aren't particularly relevant, I don't think. The difference is that I have used text in the load script (and other tests as a variable and a calculated dimension in my chart) to coerce DataVersion and the calculated substrings to text values.

[Albert_Candelario · Accepted solution]

Hello all,

Kindly also  be aware of this:
Change to MakeWeekDate function in Qlik Cloud - Qlik Community - 2011410

Cheers,

Albert