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.
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.
[ removing as errant ]
Thanks Phillip,
I had tried that however still wasn't populating correctly so I was looking for another way to calculate it.
Thank you
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)
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-DateTimeFormat-is-incorrect/td-p/46192
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.)
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.
