DateTimeFormat how to adjust?

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!

Workflow

Accepted answers

apathetichell

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

All comments

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.

apathetichell

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703