Formula - Dynamic column Dates

[suby]

Hi All,

Could some one through some Ideas on how to achieve this.

Attached the sample flow with the expected output and the output has to be dynamic based on current Date.

[Luke_C]

Hi @suby 

Take a look at this, I assumed that since we're in April, you are processing March now and considering that the current month.

I used the date time functions to figure out the difference between the current month (April) and the dates in the file. If the difference is 1, that indicates the current month you're working on, if it is 2 then it is the previous month.

[suby]

Hello,

Thanks for your solution and could you please explain what exactly the 'Month Difference' formula Does.

Also when I added the Month of  April 30-04-2021 I'm  getting null values. - sorry if I'm missing something. attached the workflow.

[Luke_C]

Hi @suby 

The month differences is figuring out how many months have passed from the current date to the date in the data.

Can you provide more requirements on how exactly you want this to work? Would you expect April to show as current month or March?

[suby]

Hello,

Yes you're right March would be my current Month is march  and Feb would be my Previous Month.

On my Data set I have combined multiple files as on one single Data set say 3 months of Data using a macro since the schema is different for each files.

Say i have a file with 3 months of Data Say on single file (Feb, March, April) and data will be added going forward to the file and i have a requirement to calculate or compare few Data points say what has changed from Previous month(Feb) and  current Month( march)  and this has to be dynamic going forward say when we add the April Data to the file move on to May the comparison has to be against (March) Previous Month and Current Month (April) data.

Hope this is clear let me know if you're stuck btw thanks for your time.

[Luke_C]

Thanks for confirming, the file that I provided is dynamic, so in 2 days when it's May, the April records should no longer show as Null, they will show with 1 and be labeled as current month, whereas the March will become Previous, and February will turn Null. You can modify the if statement as needed or filter the data to remove the null records. 

[suby]

Thanks Luke and could you please help me with how to test the data I mean in my test data I want to hardcode the dates and test your logic rather than waiting for May  it just for testing purpose.

Could you please.

Many Thanks

[Luke_C]

hi @suby 

See attached, I added a second container where I hardcoded a May date to see that April is marked as current and March is previous. The february dates show NULL, so you can either filter them out or update the if statement. The last Else clause is setting the value to null.

[suby]

Hi Luke 

,

Apologize for coming back to you late on this.

I have one final question how we can handle this when the current Date is in February 2021.

In that case January 2021 is coming up as current month which is right  and December 2020 is showing up Null  can you please help.

Because we will be adding more Data going forward we will end up in a scenario where the current Date is Feb 2022 and we need to handle this..

Attached the workflow with New Container.

[Luke_C · Accepted solution]

Hi @suby 

See attached, I updated your new container's formula to handle negatives. I also want to call out that there are definitely other approaches with datetime functions, however looking at months gets a little tricky (see below thread)

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Inconsistency-between-DateTimeAdd-and-...