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!
Solved! Go to Solution.
Hi @Astro55 , this should help with the first part:
@Astro55 and this should help with the second
@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.
@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?
@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.
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
@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')
@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).