Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
ALTER.NEXT:

Join us on Dec 2 for a half-day virtual analytics + data science event!
US & CA customers only

SAVE YOUR SPOT
It's the most wonderful time of the year - Santalytics 2020 is here! This year, Santa's workshop needs the help of the Alteryx Community to help get back on track, so head over to the Group Hub for all the info to get started!
SOLVED

DateTimeDiff in Weeks

Highlighted
6 - Meteoroid

Hi,

 

I keep getting null value from DateTimeDiff function.

 

I want to calculate the week difference between product launch week and today.

 

Below are my formula and data:

1.png

2.png

 

Please advise.

 

Thanks,

Claire

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@nyanyanunu,

 

The reason for that is that the unit of "weeks" is not supported.  You would have to use "days" and then divide by 7.  This won't get you whole numbers.  You'll need to use something like Ceiling() to get it to round up.

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
11 - Bolide

Like MarqueeCrew stated, if you want it tp round up always:

 

Ceil(DateTimeDiff([Today],[Date],'days')/7)

 

or if you want it to round down always:

 

Floor(DateTimeDiff([Today],[Date],'days')/7)

 

or if you want it to round based on the number of days:

 

Round(DateTimeDiff([Today],[Date],'days')/7,1)

Highlighted
6 - Meteoroid

Thank you so much Mark! I'm able to get the correct numbers now Cat Embarassed

Highlighted
6 - Meteoroid

Thank you so much for your deailed explination! Celi finction fits my situationHeart

Highlighted
5 - Atom

Years later and this is helpful but TODAY isn't working for me. I have only been using Alteryx a few days so maybe its something obvious. I tried:

 

 

Round(DateTimeDiff([Today],[Date],'days')/7,1) it is erroring as "Unknown variable "Today"

Highlighted
5 - Atom

I found the answer so posting it here for other rookies!

 

Today has to be somewhere in the workflow so I had to add a separate formula for Today which was DateTimeToday() and then my calculation formula Round(DateTimeDiff([Today],[Date],'days')/7,1)

 

 

Highlighted
6 - Meteoroid

I have used the formula suggested to convert days to weeks (round(....)) and I still get a "null" result...any ideas?

Highlighted
6 - Meteoroid

ignore that...although in the formula tool it says "null" it does seem to have worked when i have run it again

Labels