Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all
I am using the weekname function to get a combination of the year and the week from a date. However, the resultant week returned by the weekname function is one week behind the week function. I assume the weekname result is wrong. Below is what I mean:
The date in the above screenshot is actually the same as in the help file's example (which returns 2013/02 when they do it (which is what I want)):
We don't change any of the initial variables that are used for calendars and I would expect the Week and Weekname functions to be affected by it the same way anyways.
I'm not sure if anyone has experienced the same thing or if you can let me know whether you get different results. I would like to see if it is something related to Qlik or whether it is related to our setup.
Thank you in advance!
hi @Mauritz_SA ,
I hope this is useful for you
Check the date format in environment variables in initial script editor tab based on the input you give
- if you give the input like weekname('2013/01/12')
SET DateFormat='YYYY/MM/DD';
- if you give the input like weekname('01/12/2013')
SET DateFormat='MM/DD/YYYY';
- if you give the input like weekname('12/01/2013')
SET DateFormat='DD/MM/YYYY';
it's working for me.here is the output i got
regards,
raji
The issue seems to be with how the default values of parameter first_week_day are taken in these functions.
- WeekDay() - default value would be taken from the script variable FirstWeekDay , however
- Week() - default value is Monday [ 0=(Monday)]
That is where the issue is. If you set your FirstWeekDay value to 0 (i.e- equivalent to Monday)
your problem should go away.
Thank you both for your replies.
@tresesco, I am not sure if that is the issue. Below is what I have in script:
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
However, below is a table of week and weekname functions:
What I find strange is that the default Week and Weekname functions are exactly one week apart (so I don't think the FirstWeekDay is the issue). It is almost as if Weekname takes the first full week (unbroken week) as its first week. Unfortunately I tested that and found that I get the following and now I am convinced that there is something wrong with my weekname function as it shows that 1995/01/01-1995/01/07 is 1994/52:
I have only used the default settings in QSD June 2019 (although the same is happening on the server which is April 2019). Below is the script I used:
TestDates:
LOAD TestDate,
Week(TestDate),
Weekname(TestDate),
Week(TestDate,6,1,0),
Weekname(TestDate,0,6),
Date(TestDate,'WWWW')
INLINE [
TestDate
1994/12/28
1994/12/29
1994/12/30
1994/12/31
1995/01/01
1995/01/02
1995/01/03
1995/01/04
1995/01/05
1995/01/06
1995/01/07
1995/01/08
1995/01/09
1995/01/10
1995/01/11
];
Do you mind testing what your output is with the same values for FirstWeekDay, BrokenWeeks and ReferenceDay?
I'm a bit baffled 🙃...
Hi @tresesco
The reason why I said I don't think the FirstWeekDay is the problem is because both weeks start on the same day. But I changed it anyways:
Below is the resulting output:
As you can see there is still exactly one one week difference.
Since you are dealing with all sorts of weeks (even the last ones of a year), you should also consider the broken week variable. Try like:
SET FirstWeekDay=0;
SET BrokenWeeks=0;
Thank you once again for the replies. My requirement is to match the app's Year-Week field with Outlook's default settings (at least in our region). This is what Outlook is set to:
I have set the script variables to the following:
SET FirstWeekDay=6; -> I want the first day of the week to be Sunday
SET BrokenWeeks= 0; -> It seems like Outlook doesn't use broken weeks
SET ReferenceDay=1; -> @swuehl , thanks for pointing this out. When I looked at Dec 1997 and Jan 1998 I realised that setting this to four is the equivalent of the First 4-day week option in the Outlook calendar:
By using the variables above, I am able to get a perfect match to Outlook using the Week function. However, the weekname function remains off by 1 week for the most part and 2 weeks when looking at 1994/12/28 - 1994/12/31.
The reason why I am asking all of these questions is because I have added a Year-Week dimension to our autocalendar script and I used weekname() for the function as it returns a dual which is nice for calculations where the year is relevant too. The question I have now is whether I should rather build a function using Dual(Year(TestDate)&'/'&Num(Week(TestDate),'00'),WeekStart(TestDate)) or whether there is something I am missing with the built in weekname function which is returning the wrong week. I feel like the custom function will work better, but I also don't want to use a workaround if there is a flaw in my understanding or configuration.
Here is the script I used for the table above (and I also included the custom function I mentioned above):
SET FirstWeekDay=6;
SET BrokenWeeks= 0;
SET ReferenceDay=1;
TestDates:
LOAD
Usecase,
TestDate,
Week(TestDate),
Weekname(TestDate),
Dual(Year(TestDate)&'/'&Num(Week(TestDate),'00'),WeekStart(TestDate)),
Date(TestDate,'WWWW')
INLINE [
Usecase,TestDate
1,1994/12/28
1,1994/12/29
1,1994/12/30
1,1994/12/31
1,1995/01/01
1,1995/01/02
1,1995/01/03
1,1995/01/04
1,1995/01/05
1,1995/01/06
1,1995/01/07
1,1995/01/08
1,1995/01/09
1,1995/01/10
1,1995/01/11
2,1995/12/28
2,1995/12/29
2,1995/12/30
2,1995/12/31
2,1996/01/01
2,1996/01/02
2,1996/01/03
2,1996/01/04
2,1996/01/05
2,1996/01/06
2,1996/01/07
2,1996/01/08
2,1996/01/09
2,1996/01/10
2,1996/01/11
];
Thanks again for all the helpful input, I really appreciate it.
