Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
Mauritz_SA
Partner
Partner

Week and weekname functions giving different results

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:

Qlik Sense exampleQlik Sense example

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)):

Help fileHelp file

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!

@tresesco @Vegar 

11 Replies
raji6763
Creator II
Creator II

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';

 

raji6763_1-1592218702633.png

 

 

it's working for me.here is the output i got

raji6763_0-1592218574773.png

regards,

raji

tresesco
MVP
MVP

@Mauritz_SA ,

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)

Capture.PNG

your problem should go away.

Mauritz_SA
Partner
Partner
Author

Hi @raji6763 and @tresesco 

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:

Dates.png

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:

Dates2.png

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 🙃...

tresesco
MVP
MVP

Change the variable value like:

SET FirstWeekDay=0;            

and reload the app. Then check the output at the UI.

Mauritz_SA
Partner
Partner
Author

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:

Load_Script.png

Below is the resulting output:

Dates3.png

As you can see there is still exactly one one week difference.

Mauritz_SA
Partner
Partner
Author

Perhaps @rwunderlich@swuehl or @sunny_talwar have an idea?

tresesco
MVP
MVP

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; 

tresesco_0-1592552137867.png

swuehl
MVP
MVP

Also consider that ISO 8601 requires reference day set to 4.

Note that Week() by default uses ISO 8601, while weekname looks at the script variables.

But you can use the optional Week() function parameter to tweak accordingly:

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/Scripting/DateA...

Mauritz_SA
Partner
Partner
Author

Hi @swuehl and @tresesco 

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:

Outlook.png

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:

Four day week in Outlook.png

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.

Qlik_vs_Outlook.png

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.