Dynamically select & rename columns based on current date (column headers are dates)

Hello All,

I need help with two parts of a problem that I am trying to solve. I have added a file with two tabs - the first contains the raw data and the second contains the expected output

Part 1
I have a table that lists various deals and their projected sales over several months. I want to perform further calculations on only those columns that are from the current month onwards. For example, if I'm running the workflow on 7/27/2023, I want Alteryx to select all columns from Jul-23 onwards, and exclude anything before that.

I tried performing a dynamic select with the formula listed below, but it's not working. Could you please help me develop an expression to dynamically select the first column + all columns from the current month onwards?

Expression that I tried in the Dynamic Select Tool:
[FieldNumber] >= DateTimeTrim(DateTimeToday(),'firstofmonth')

Part2
I then want to rename those column headers to consecutive numbers from 1 onwards, so that I can set formulas to add certain columns within the data. I tried researching old posts but couldn't figure out how to easily do this.

Any help would be greatly appreciated!

Example - Dynamic Col Select.xlsx

Date Time

Help

Accepted answers

FinnCharlton

@Astro55 and this should help with the second

Screenshot 2023-07-27 155920.png

Dynamic Select and Rename.yxmd

All comments

FinnCharlton

Hi @Astro55 , this should help with the first part:

Screenshot 2023-07-27 155643.png

Dynamic Select.yxmd

FinnCharlton

@Astro55 and this should help with the second

Screenshot 2023-07-27 155920.png

Dynamic Select and Rename.yxmd

Astro55

@FinnCharlton - The second solution works like a charm! Thank you SO SO much!

I see how perfectly the first solution works in your workflow. But for some reason, when I apply it to my data, it's only pulling in the first column and none of the date columns. Is it because I used the exact same expression as you and used "[name]" instead of some other col. header? I couldn't figure out what else to use in its place.

FinnCharlton

@Astro55 In the Dynamic Select tool, [Name] just refers to the column name, so there shouldn't be a problem there. You might be using a different date format to me, which would result in the dateparse formula being null. What exactly is your date format in the column names?

Astro55

@FinnCharlton - Firstly, Thank you SO much for the super swift response!

When I click into the columns, the date format is M/D/YYYY (e.g., 7/1/2023 or 10/1/2023), and shows up as Jul-23. Could this be causing the issue? Apologies, Date-Time still confuses me a lot in Alteryx.

Astro55

PS: I tried changing the date format to the standard Alteryx format of yyyy-mm-dd, but Alteryx is still not picking up any of the date fields

FinnCharlton

@Astro55 so the name of the field is in M/D/YYYY format? Where does it show up as Jul-23? Maybe a screenshot would be helpful. If the column names are in M/D/YYYY format, you can replace the datetimeparse function in the dynamic select tool with:

datetimeparse([Name],'%m/%d/%Y')

Astro55

@FinnCharlton : I did a little more digging and Alteryx is importing the dates correctly. I tried using the updated formula and playing around with the date-time formula, but I'm hitting a blank wall. Screenshots and file below (tab: Input).

Screenshot 1.jpg

Screenshot 2.jpg

Example - Dynamic Col Select.xlsx

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15865

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703