topic Re: Week and weekname functions giving different results in App Development

Week and weekname functions giving different results

Mauritz_SA — Mon, 15 Jun 2020 07:53:11 GMT

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 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 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!

@tresB @Vegar

Re: Week and weekname functions giving different results

raji6763 — Mon, 15 Jun 2020 11:09:46 GMT

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

Re: Week and weekname functions giving different results

tresB — Mon, 15 Jun 2020 14:05:05 GMT

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

your problem should go away.

Re: Week and weekname functions giving different results

Mauritz_SA — Wed, 17 Jun 2020 08:43:08 GMT

Hi @raji6763 and @tresB

Thank you both for your replies.

@tresB, 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 🙃...

Re: Week and weekname functions giving different results

tresB — Wed, 17 Jun 2020 09:07:15 GMT

Change the variable value like:

SET FirstWeekDay=0;

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

Re: Week and weekname functions giving different results

Mauritz_SA — Wed, 17 Jun 2020 13:06:04 GMT

Hi @tresB

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.

Re: Week and weekname functions giving different results

Mauritz_SA — Fri, 19 Jun 2020 00:20:43 GMT

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

Re: Week and weekname functions giving different results

tresB — Fri, 19 Jun 2020 07:36:41 GMT

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;

Re: Week and weekname functions giving different results

swuehl — Tue, 23 Jun 2020 13:33:14 GMT

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/DateAndTimeFunctions/week.htm

Re: Week and weekname functions giving different results

Mauritz_SA — Tue, 23 Jun 2020 21:19:07 GMT

Hi @swuehl and @tresB

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.

Re: Week and weekname functions giving different results

swuehl — Wed, 24 Jun 2020 16:37:24 GMT

Which Qliksense version are you running? Have you already tried with the latest June 2020 SR1?

Re: Week and weekname functions giving different results

Mauritz_SA — Wed, 24 Jun 2020 19:39:41 GMT

Hi @swuehl

I am currently on June 2019 (as our server hasn't been updated yet). I will do an install of June 2020 tomorrow and see if it fixes the issue. Thanks for the help.

Regards,

Mauritz