Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer and Intelligence Suite.

DateTimeDiff("2022-10-24",[TodaysDate],"days")/7

tpostlewate
7 - Meteor

Hi! I have a question regarding the date in this formula. I took the project over, and no one seems to know what that date is doing in the formula. It recently gave me a wrong date- and I am trying to determine if this date in the formula is throwing stuff off. It did work, until this week. Can anyone help me understand exactly what this is doing with that particular date?

 

Thanks!

Terri

9 REPLIES 9
dYoast
10 - Fireball

@tpostlewate 

 

I'm taking a WAG.

I think it was determining if the day was a Monday - based on the fact that 2022-10-24 is a Monday.

 

How is the result used in the workflow?

Carlithian
8 - Asteroid

So the datetimediff() formula should return an int that represents the difference between two dates, depending on the units selected, assuming that the field [todaysdate] is an actual date field, that would give you -2 as the first date is two days before the second, then divided by 7 you'd get -0.28...

 

I would guess that the "2022-10-24" has been changed recently and will be the cause of your problem, as I can't think of a reason as to why this formula would be used.

 

tpostlewate
7 - Meteor

That is what is so perplexing. This has not been changed in YEARS. In fact, until it broke last week and put the wrong date on the file, I would have never known it was there. If I take this date out, what do I put in its place?

tpostlewate
7 - Meteor

In the end- it is supposed to put the Monday date on the file. So, this week, it will be 10/24/2022, even though this is a bad example, since it didn't work AND that date lines up with the date in the formula. (better example- last week's file would have been labeled 20221017, and so on) Literally, this process has been around for about 5 years. Was this an arbitrary number that just finally hit the actual date? I need to figure out what needs to be substituted for that 10/24/2022 date.

tpostlewate
7 - Meteor

Here is the entire formula:

 

tpostlewate_0-1666819172843.png

tpostlewate_2-1666819272929.png

.

For some reason, the file date is coming back as 20221021, when it should be 20221024.

binuacs
17 - Castor

@tpostlewate Seems to be your formula looking for the previous monday. One way of doing this

 

binuacs_0-1666823800901.png

 

grazitti_sapna
17 - Castor

@tpostlewate Seems like you want to get the start of week corresponding to your dates and show the data on weekly basis. For this you can use the below workflow. and if you want the weekday name then u can use the below formula for it.

 

grazitti_sapna_0-1666848603004.png

 

Sapna Gupta
ShankerV
13 - Pulsar

hi @tpostlewate 

 

I am trying to break down the formula you have issue with.

 

Re: DateTimeDiff("2022-10-24",[TodaysDate],"days")/7

 

DateTimeDiff is the formula used to difference between two dates, D1 and D2, 

days will result the output in number of days. We can use months too.

Dividing by 7 helps to divide the output / 7.


If the result is Zero, then it might be used as the a primary output to derive some logic in your workflow.

 

Hope this helps!!! Let me know if more support needed!!!

dYoast
10 - Fireball

@tpostlewate , You can delete all of the formulas except FileDate and simply use the formula that @binuacs provided.

That will allow your workflow to work as planned and you will never have to change the hard-coded date.

 

That was an interesting method to determine the start of the week as Monday, but the hard-coded date caused a problem.

 

If you just want to change the hard-coded date, you can set it to any Monday in the future such as 2025-01-06.

Labels