Regarding Dynamically change year and month in alteryx

[PiyushJain]

Hi Team 

Good Morning, 

This is regarding dynamically changed year and month formulas in alteryx. 

My requirement is : Every month i need to replace month automatically and then workflow run as per scheduled time.

I refer the relevant articles/ post,but unfortunately i am not able to resolve my issue.

Actually in my case ,

Below 2 coloumns are coming from database:

1. Year

2. Mon

( There is no proper date coloumn coming from Database) 

I referred related articles and found there is a proper date in ddmmyyyy format. 

But in my case i have only above 2 coloumns.

How can i use these 2 coloumns and dynamically change the months.

Please advise

Regards 

Piyush J

[RolandSchubert]

Hi @PiyushJain ,

you can create the month or year string using DateTimeFormat, a statement could look like this:

DateTimeFormat(DateTimeToday(), '%Y')  -> returns the year a a string, currently "2022"

or

DateTimeFormat(DateTimeToday(), '%m') -> returns the month as a string, currently "01"

Let me know if it works for you.

Best,

Roland

[atcodedog05]

Hi @PiyushJain 

I am guessing you are getting year and month and you want to merge to get a single value year+month and compare with the current month.

Here is how you can do it.

Workflow:

Or

Depending on the format. And Alteryx only accept date format in yyyy-mm-dd.

Hope this helps : )

Can you provide some sample input and expected output It will help us get a better understanding of the use-case.

We will be happy to help : )

[PiyushJain]

Thanks Ronald and atcodedog ,

This is realy helpful.

But i always need last month data ( not the current month)

Please advise how to change the formulas. 

Regards 

Piyush Jain

[atcodedog05]

Hi @PiyushJain 

We can use summarize tool to get max month and use it for reference to filter on latest month.

Workflow:

1. Using formula tool to convert to date.

2. Using summarize tool to get max date(latest month).

3. Using append tool to add the lastest month to data.

4. Using Filter tool to filter only latest month data.

Hope this helps : )

[RolandSchubert]

Hi @PiyushJain ,

DateTimeFormat(DateTimeAdd(DateTimeToday(), -1, 'month') , '%m') return the last month. 

Best,

Roland

[atcodedog05]

Hi @PiyushJain 

(After checking @RolandSchubert solution) Oops, my bad if you want last month-yyyy based on the current month-yyyy use the below workflow. Guess there is different ways how a statement can be interpreted 😅

As a best practice please provide some sample input and expected output It will help us get a better understanding of the usecase and help you better 🙂

Hope this helps : )

[PiyushJain]

Hi Ronald,

Soory for the late reply..  

I go through this formula and able to get month like 12 in output.

But not able use / understand next formula ,so that i can connect this new formula with my month coloumn. 

Because ultimately i need yo use database coloumn to connect with new formula . And thus can automate so that after every monthly run i can get previous month data.

In other word ,how my database coloumn know to provide previous month data.

I tried to use IIF function, but getting error.

Please advise.

Regards

Piyush J

[atcodedog05]

Hi @PiyushJain 

Use the formula and then use filter as [Is Last Month]="Yes" like below

Workflow:

Hope this helps : )

[PiyushJain]

Thanks atcodedog05, But how this " is this last " coloumn i can relate with my database coloum name that is " Mon" Can we use logic like If " Mon" = "Is last Month " Then " Yes" else " No" Like that logic i m looking for. Regards Piyush J