topic Re: Week number not working ?? in QlikView

Week number not working ??

madhuqliklondon — Wed, 25 Nov 2020 16:16:04 GMT

Hi Friends,

I have column 'OrderDate' with 20170701 as data , I am converting that into dates by below script

Date(Date#([OrderDate],'YYYYMMDD'),'DD/MM/YYYY') as 'OrderDate,

Year(Date(Date#([OrderDate],'YYYYMMDD'),'DD/MM/YYYY')) as Year,

Month(Date(Date#([OrderDate],'YYYYMMDD'),'DD/MM/YYYY')) as Month,

Week(Date(Date#([OrderDate],'YYYYMMDD'),'DD/MM/YYYY')) as Week,

Day(Date(Date#([OrderDate],'YYYYMMDD'),'DD/MM/YYYY')) as Day

but week number is not working properly, For example dates from 3rd July to 9 July it suppose to be week number 27 ,but for me it shows up as 28 but year,Month ,Day are loading correctly. Is there any thoughts about this or do I have to use Makedate function how do I use it here? please advice. Many thanks.

Re: Week number not working ??

Chanty4u — Tue, 01 Aug 2017 08:54:19 GMT

Am not sure,

May be in your data 28th week data is there? did you check that?

Re: Week number not working ??

madhuqliklondon — Tue, 01 Aug 2017 08:57:17 GMT

Yes there is data, I had that doubt but there is data for those dates .

Re: Week number not working ??

Chanty4u — Tue, 01 Aug 2017 09:00:01 GMT

if there is data it will show in the week field . thats why week 28 is showing i believe.

Re: Week number not working ??

tomasz_tru — Tue, 01 Aug 2017 09:02:15 GMT

Maybe week start is redefined in the script?

Redefining the Week Numbers

Re: Week number not working ??

Chanty4u — Tue, 01 Aug 2017 09:06:07 GMT

As Tomasz Truszkowski suggested

check in script variables

Re: Week number not working ??

Anil_Babu_Samineni — Tue, 01 Aug 2017 09:13:13 GMT

Better. If you provide sample data to test rather Context and meanwhile, This looks ambiguous to me because not working properly means where you done this?

Re: Week number not working ??

madhuqliklondon — Tue, 01 Aug 2017 09:18:31 GMT

Yes . I have changed

SET FirstWeekDay=0;

SET BrokenWeeks=0;

SET ReferenceDay=4;

now it works fine.

Thank you chanty4u‌ and @ Tomasz

Re: Week number not working ??

Chanty4u — Tue, 01 Aug 2017 09:19:26 GMT

nice issue got resolved