Alteryx designer Discussions

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

how to get the current month by number

Highlighted
Asteroid

The data type of month information in this table is "Double", I want to pull out the POS for all the month that before the current month...

 

MonthPOS
1300
2200
3450
4680
5200
6200
7100

 

For example, the current month is June, then pull out POS for (Jan - May)

I am using the filter function to adjust the numbers by monthly to pull out this dat.

So when we move into July, I will adjust the filter as [Month]< 7

Is there any way that I don't need to adjust it and let the system run automatically by applying this logic?  Thank you.

[Month]<Month(today())

 

Highlighted
Magnetar
Magnetar

I believe the following will get you there:

[Month]<ToNumber(DateTimeFormat(DateTimeToday(),"%m"))

Let us know if that works?

NJ

Highlighted

If you want to have a 'Month' function, I have put together some functions to extend Alteryx: https://github.com/jdunkerley/AlteryxFormulaAddOns/releases/latest

 

Once installed you should be able to do:

[Month] < Month(DateTimeToday())

 

Which is equivalent to @NicoleJohnson answer

Asteroid

@NicoleJohnson 

@jdunkerley79

Hi Nicole & jdunkerley79,

I was very happy that this formula works great when I got the answer from you.

[Month]<ToNumber(DateTimeFormat(DateTimeToday(),"%m"))

[Month]<month(datetimetoday())

 

Recently,  I had the same situation again and want to use this formula but under "in database", but it doesn't works....

 

I received this syntax error: expected something between the 'where' keyword and '['.

 

then I changed [] into " "

"Month"<ToNumber(DateTimeFormat(DateTimeToday(),"%m"))

and here is the syntax error: data type "datetimetoday"does not match a defined type name...

 

"Month"<month(datetimetoday())

 syntax error; expected something between '(and ')'

 

I also tired to remove "TO number" since the data type for month is already 16 intl, but still doesn't work......

 

 

Could you please let me know how to fix this....?

How do you learn what modification do you need when using the same formula under "in database" and not "in database"..

There are always some differences even thought it is same data...

thank you.

 

 

Labels