Hello!
How Do I dynamically select Colummn that is Date in my excel sheet (5/31/2021, 4/30/2021, etc) and Now I have to select last date that is 5/31/2021 minus 4/30/2021. As of Now I am doing manually - select those field in formula and did my calculation but next month there will added new field 6/30/2021 and I need data from 6/30/2021 minus 5/31/2021 - I am trying to do dynamically - so every month I don't have to open up workflow and change it manually.
Please advise. Thank you for your co-operation.
Solved! Go to Solution.
Sure - Thank you.
Corporation 3/31/2021 4/30/2021 5/31/2021
ABS 100 500 600
Google 600 400 700
Alteryx 700 1000 1200
Now I want create a WF that pickup last 2 dates field for calculation, ie [5/31/2021]-[4/30/2021] and then I will dynamically rename that field with all answer for further processing. Currently I am doing manually, every month. hope that helps!
Hi ,
Did you try using interactive tools the date column in alteryx deigner
Here's one approach. I transpose the data and convert the date column headers to dates that alteryx can read. Then I sort by Corporation/date and use the sample tool to take the first two dates (latest dates) for each corporation. Then I use the crosstab to put the dates in separate columns that you can use for further calcs.
Thank you that works. But How do I select automatically in Formula tool instead of select like [2021_05_31]-[2021_04_30]
becasue next month when I run my process another date column 2021_06_30 will be added with different data and then I have to select [2021_06_3]-[2021_05_31], and so on...
is there a way I can use date function for date1 - date function for date2 - because this date column is always End of the Month date.
Thank you for your help! Appreciate it.
Yes, here's an updated version. I used the tile tool to assign values of 1 and 2 to the dates and used those as the column headers. These then are used in the formula, and later renamed back to the dates using a dynamic rename.
I used dynamic Select Tool & select Option - Select via a Formula and under variable there is fieldnumber and under function there is a Operator but when I tried Fieldnumber=3 minus Filednumber=2 - should work but it doesn't work
Yes the select via formula is more to select columns that meet some criteria, i.e. containing a certain value in the name. It's not to perform operations on the data itself. Let me know if the updated version I just posted works for you.
Thank you so much! Appreciate it.
Save a lot of time and works wonderful - it saved 3 manual steps in my workflow.
Have a great day!