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

Calculating the week number

Talbanese
7 - Meteor

Hello,

 

Has anyone had luck on calculating the week number from a date?

 

example: 2016-02-01 is the 6th week of the year

 

Appriciate anyones help with this.

 

Thank you.

8 REPLIES 8
Philip
12 - Quasar

Is this what you're looking for?

 

In the Formula tool, use the expression DateTimeFormat(<date/time>, "%U")

 

From the Date Time Functions (http://help.alteryx.com/9.5/Reference/DateTimeFunctions.htm)

 

%U

This will return the week number, as 00 – 53, with the beginning of weeks as Sunday.

 

%W

This will return the week number, as 00 – 53, with the beginning of weeks as Monday.

Harsha
6 - Meteoroid
Hi Talbanese, Find the attached workflow, you can find how we can generate Year, Quarter, Month, Week Number and a Weekday . Thanks, Harsha.
JohnJPS
15 - Aurora

[ removing as errant ]

Talbanese
7 - Meteor

Thanks Phillip,

 

I had tried that however still wasn't populating correctly so I was looking for another way to calculate it.

 

Thank you

jlefeaux
8 - Asteroid

Hi all,

 

I can confirm that  DateTimeFormat([Date],"%W") does not return the correct ISO week number (https://en.wikipedia.org/wiki/ISO_week_date)

 

[Date]ISO Week NumberDateTimeFormat ("%W)
Week Number
2017-01-015200
2017-01-020101
2015-12-285352
2015-12-305352

 

The week numbers returned in the accepted solution aren't all ISO standard either (2017-01-01 returned week 2).

 

BUT @JohnJPS posted a single formula that correctly calculates the ISO week number here: https://community.alteryx.com/t5/Data-Preparation-Blending/Calculating-Week-Number-using-DateTimeFor...

 

I'm late to the party, but I'm posting this in case other people come to this thread looking for ISO week numbers. I've attached a workflow which has the test values above and a series of calculations that determine the correct ISO week number (basically @JohnJPS's formula broken down into steps--he has the same steps in his own sample workflow.) 

NickR82
5 - Atom

Hello All,

 

A little late to this conversation. I believe the ISO weeks were off in 2015 due to an anomaly that causes a 53 week on most fiscal calendars every 6 years.or so. I am struggling with a similar issue being that I do not have a full date for my weeks from previously stored data. The data is in the format of wk/yyyy is it possible to use the %W or %U  Function on this format to make it into weeks?

 

Thanks,

Nick

jlefeaux
8 - Asteroid

Hi Nick,

 

Are you trying to get the week number from the wk/yyyy format? Since that will be a String and not a Date data type, the Date/Time functions won't work.  Instead you could use a Test To Columns tool to split up the two parts and then convert each to a number, either with a Select tool or the ToNumber() function--In the attached workflow, I used a Select tool so that I could rename the columns at the same time.

 

Hope this helps,

Jennifer

sksatish
5 - Atom

hello team,

when fiscal weeks, Quarter,sales_amount  are there how can we get average of sales-amount with respect to number of fiscal weeks updated in that quarter

 

example : Q1=FW1 to FW13

 Q1=sum(sales_amount)/FW count = 331/13=25.461.

consider Q2 and  Q3 as above 

now coming to Q4 as this quarter is in progress we had only count of FW as 11

based on next week this count gets increment 

we need the above calculation similar to this dynamically.

 

Thanks.

 

 

 

Labels