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

DateTimeFormat how to adjust?

Joker_Hazard
11 - Bolide

Dear all, Hello!

I am having some issues with this formula:

Joker_Hazard_0-1623429064398.png


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!

7 REPLIES 7
BrandonB
Alteryx
Alteryx

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
18 - Pollux

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
Alteryx
Alteryx

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

apathetichell
18 - Pollux

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

Joker_Hazard
11 - Bolide

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

Joker_Hazard
11 - Bolide

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?

Joker_Hazard_0-1623434621818.png

 

apathetichell
18 - Pollux

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

Labels