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.

ISO WeekNumber Calculation which gives the WeekNumbers from Week 1 to till present Week.

stj1120
8 - Asteroid

Hi all,

 

I need the calculation which will calculate the ISO WeekNumbers from Week1 to till the current week. 

 

If  anyone has this logic, kindly share it. 

 

Thanks in advance.

 

Br,

Sreenivasa Teja.

4 REPLIES 4
BrandonB
Alteryx
Alteryx

Take a look at this one: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Convert-date-to-week-number-of-that-ye...

 


PadLeft(ToString(FLOOR(DateTimeDiff(DateTimeAdd([Field1],4-Switch(DateTimeFormat([Field1],"%a"),7,"Mon",1,"Tue",2,"Wed",3,"Thu",4,"Fri",5,"Sat",6),"days"),DateTimeParse(ToString(ToNumber(DateTimeFormat(DateTimeAdd([Field1],4-Switch(DateTimeFormat([Field1],"%a"),7,"Mon",1,"Tue",2,"Wed",3,"Thu",4,"Fri",5,"Sat",6),"days"),"%Y")))+'-01-01',"%Y-%m-%d"),"days")/7)+1),2,"0")

 

which lines up with ISO which is perhaps the most accepted standard.


 Give this a try and let me know if it helps

stj1120
8 - Asteroid

Hi @BrandonB 

 

Thank you for your quick response. 

 

I know this ISO WeekNumber formula. But, this gives the current weeknumber where as I need the formula which gives the results of ISO WeekNumber  from Week 1 to till the current week which is Week 15 continuously. 

 

If you can provide this logic that would be really great!

 

Awaiting your response. 

 

Best Regards,

Sreenivasa Teja.

AbhilashR
15 - Aurora
15 - Aurora

Hi @stj1120, it has been a few days since you posted this question, so not sure if you are still looking for a solution. I found some elegant solutions posted on these community pages which will give you ISO week numbers for a given date. I have attached an implementation of these formulas it is helps.

 

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Convert-date-to-week-number-of-that-ye...

Or this article 

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Calculating-Week-Number-using-DateTime...  

 

Hope this helps, and thanks for posting this question!

digitalmemo
8 - Asteroid

@BrandonB I've been trying to figure this out for a long time. This formula is what I need. Thank you!

Showing you my tedious logic to get FSCL and CAL Week in the attached file. Is it possible to convert my work to a formula as well? The rule is FSCL_WEEK resets per year on Monday and CAL_WEEK resets per year on Sunday.

Labels