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

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.

Best Practices

Accepted answers

Luke_C

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.

All comments

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.

New Workflow3.yxmd

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.

New Workflow3.yxmd

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703