Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

How to receive the same week number as is in excel ?

JustynaMZ
7 - Meteor

Hi,

 

I was trying few solutions but still did not receive the result I am looking for.

 

I have a report with data from few years.

 

We want to receive comparison week to week for each calendar year.

 

As previously we used excel to count number of weeks I would like to receive now the same week number as it appears in excel.

I was trying to use Week number formula, week number + 1 and 2 more 'tricks' I found here. But still do not receive the same results.

 

Do you have any advice what to try?

 

Thanks in advance for your help!

 

 

7 REPLIES 7
Kenda
16 - Nebula
16 - Nebula

Hello @JustynaMZ 

 

Have you tried the DateTimeParse() or DateTimeFormat() functions?

 

Use this webpage to get more details: https://help.alteryx.com/current/designer/datetime-functions 

messi007
15 - Aurora
15 - Aurora

@JustynaMZ,

 

Could you share some simple data with the expected result in order to help 🙂

I had some issue using datetimediff with weeks I changed to days in order to flag weeks

 

DateTimeDiff([EndDate],[StartDate],"days")

 

Hope that helps,

Regards, 

JustynaMZ
7 - Meteor
Application Date and TimeWeek AlteryxWeek Excelmatch?I would like to receive the same Week nr in Alteryx as I receive in XLSX
01.01.2015 00:0011yes 
02.01.2015 00:0011yes 
03.01.2015 00:0011yes 
04.01.2015 00:0012no 
05.01.2015 00:0012no 
06.01.2015 00:0012no 
07.01.2015 00:0012no 
08.01.2015 00:0022yes 
09.01.2015 00:0022yes 
10.01.2015 00:0022yes 
11.01.2015 00:0023no 
12.01.2015 00:0023no 
13.01.2015 00:0023no 
14.01.2015 00:0023no 
15.01.2015 00:0033yes 
16.01.2015 00:0033yes 
17.01.2015 00:0033yes 
18.01.2015 00:0034no 
19.01.2015 00:0034no 
20.01.2015 00:0034no 
02.03.2019 21:4499yes 
02.03.2019 22:3099yes 
02.03.2019 22:3299yes 
02.03.2019 22:3499yes 
02.03.2019 22:4399yes 
02.03.2019 23:2599yes 
02.03.2019 23:3199yes 
02.03.2019 23:3199yes 
02.03.2019 23:4099yes 
03.03.2019 00:08910no 
03.03.2019 00:14910no 
03.03.2019 00:25910no 
03.03.2019 00:29910no 
03.03.2019 00:34910no 
03.03.2019 00:42910no 
03.03.2019 01:04910no 
03.03.2019 01:17910no 
03.03.2019 02:57910no 
03.03.2019 03:28910no 
03.03.2019 03:39910no 
messi007
15 - Aurora
15 - Aurora

@JustynaMZ,

 

Please see below:

messi007_0-1608568307950.png

Attached the workflow,

 

If this solves your issue please mark the answer as correct, it will help others!

 

Regards,

JustynaMZ
7 - Meteor

Thanks for your help.

 

Nevertheless I probably was not clear enough.

 

I do not need to compare Weeks if are the same as in Excel.

 

I have a table with column application date and time (sample above). Based on the date I want to receive 'Week Number' (the same as in column Week Excel).

 

Nevertheless when I use 'Datetimeformat' (Datetimeformat +1)I receive different week numbers than I received when I was using Excel in the past (WeekNum formula in excel). (that is why I presented these 2 additional 'Week Alteryx' and 'Week Excel' columns+ column which shows what are the differences to show where I see differences).

 

Unfortunately I cannot use 

ToNumber(DATETIMEFORMAT([Date], "%W"))+1

as it does not work for all years I have in my report.

 

To summarize:

1. I have a table with column 'Application Date and Time'

2. I want to receive column with 'Week Number'

 - but to be counted same way as in Excel.(as in the past we presented the data based on excel formula)

I do not know why alteryx shows different weeks than WeekNum in Excel 😞

 

Application Date and TimeWEEK Number?
01.01.2015 00:00 
02.01.2015 00:00 
03.01.2015 00:00 
04.01.2015 00:00 
05.01.2015 00:00 
06.01.2015 00:00 
07.01.2015 00:00 
08.01.2015 00:00 
09.01.2015 00:00 
10.01.2015 00:00 
11.01.2015 00:00 
12.01.2015 00:00 
13.01.2015 00:00 
14.01.2015 00:00 
15.01.2015 00:00 
16.01.2015 00:00 
17.01.2015 00:00 
18.01.2015 00:00 
19.01.2015 00:00 
20.01.2015 00:00 
02.03.2019 21:44 
02.03.2019 22:30 
02.03.2019 22:32 
02.03.2019 22:34 
02.03.2019 22:43 
02.03.2019 23:25 
02.03.2019 23:31 
02.03.2019 23:31 
02.03.2019 23:40 
03.03.2019 00:08 
03.03.2019 00:14 
03.03.2019 00:25 
03.03.2019 00:29 
03.03.2019 00:34 
03.03.2019 00:42 
03.03.2019 01:04 
03.03.2019 01:17 
03.03.2019 02:57 
03.03.2019 03:28 
03.03.2019 03:39 

 

 

JustynaMZ
7 - Meteor

Hi, maybe somebody will have the same problem.

So here is how I solved this one:

 

JustynaMZ_0-1608580399945.png

 

Kenda
16 - Nebula
16 - Nebula

Hi @JustynaMZ 

 

I was able to create an Alteryx week field that matched your Excel week field using the following expression:

 

tonumber(datetimeformat(DateTimeParse([Application Date and Time],"%d.%m.%Y %H:%M"),"%U"))+1
Labels