How Do I dynamically select Colummn that is Date in my excel sheet (5/31/2021, 4/30/2021)

[dalpatmistry2]

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.

[atcodedog05]

Hi @dalpatmistry2 

Can you provide some sample data.

[dalpatmistry2]

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!

[Pravallika_Pilli]

Hi ,

Did you try using interactive tools the date column in alteryx deigner

[Luke_C]

Hi @dalpatmistry2 

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.

[dalpatmistry2]

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.

[Luke_C]

@dalpatmistry2 

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.

[dalpatmistry2]

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

[Luke_C · Accepted solution]

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.

[dalpatmistry2]

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!