DateTimeFormat how to adjust?

[Joker_Hazard]

Dear all, Hello!

I am having some issues with this formula:

What I need is that "2021-06-09" should be WEEK ONE as "1", instead of 23, however it is reading based on the calendar. Is there any other workaround for that?

I still need Alteryx to read the subsequent weeks by the calendar, its just that it should start as week one instead of 23.

Tks!

[BrandonB]

What if you use the summarize tool to find the minimum date, then append this value to the data before the summarize. Then you can use a formula tool with a datetimediff to calculate the number of months from the current date to the minimum. This would effectively give you a month number starting from a minimum value. 

[apathetichell]

the "%w" parameter is a fixed value of week from beginning of the year. You are looking for floor(datetimediff([RN_Execution_Date],"2021-06-09","days"),7)+1

For a relative week comparison use datetimediff, floor divide by 7, and add 1.

[BrandonB]

@apathetichell awesome example! I always forget about the floor function

[apathetichell]

@BrandonB  FLOOR function is the batch macro of math functions,. Endless uses.

[Joker_Hazard]

Guys I will be checking both solutions in a bit.. give me afew minutes!! Thank you for both replies in advance!!

[Joker_Hazard]

Apathetic,

I am trying to use your formula, but there is a parse error. I tried to fix but I dont see any mistakes... any ideas?

[apathetichell · Accepted solution]

Sorry my syntax was slightly off:

floor(datetimediff([RN_Execution_Date],"2021-06-09","days")/7)+1

always get the syntax between mod and floor confused.

So change where I had ",7" to "/7".