Week number not working ??

[madhuqliklondon]

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.

[Chanty4u]

Am not sure,

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

[madhuqliklondon]

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

[Chanty4u]

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

[tomasz_tru]

Maybe week start is redefined in the script?

Redefining the Week Numbers

[Chanty4u · Accepted solution]

As Tomasz Truszkowski  suggested

check in script variables

[Anil_Babu_Samineni]

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?

[madhuqliklondon]

Yes . I have changed

SET FirstWeekDay=0;

SET BrokenWeeks=0;

SET ReferenceDay=4;

now it works fine.

Thank you chanty4u‌ and @ Tomasz

[Chanty4u]

nice issue got resolved