Selecting previous , current and future years

on this attachment it has 2 tabs first year data for current year and second for next year (which is in next year).. on First tab data started from 2022 so if i use multi field formula then my year will start from 2022 but in next year my data will start from year 2023. so in next year multi field formula will missing 2022 and also 2027 will be unchecked. how can i resolve this issue. my project is on going so column for year will be change in every year.

Test.xlsx

Developer

Developer Tools

Input

Accepted answers

alexnajm

I would use a Dynamic Select - you can have a logical way with DateTime formulas to select the years you need. If it's the previous year and greater, then this should work

[Name]>=ToString(DateTimeYear(DateTimeToday())-1)

NeoInfiniTech

Hi @kauser,

Attached is a sample workflow where I tried to implement the solution I explained in my previous reply. The sample process consists of the numeric values being divided by 100, which you can adjust to your own process needs. All the numeric columns are automatically processed in this manner.

However, if there are multiple string columns, you will need to adjust the Transpose column selection so that no string columns are included in the Data Columns selection, or that the additional string columns are included in the Key Columns selection if necessary. As I previously mentioned in my previous reply, please make sure that there are no rows containing string values in numeric columns. If there are such rows, make sure to filter them out and convert these columns using the Select tool to the appropriate numeric data type (such as Double).

If the sample flow I shared does not meet your needs, please also provide a target output which would be the processed version of the sample input you shared in your original post, so that the flow can be adjusted as necessary.

kauser_sample_flow.yxmd

All comments

NeoInfiniTech

Hi @kauser,

Looking at the sample data you provided, I understand that you are selecting the Numeric option in the Multi-Field Formula, which should select all the year columns as they are correctly formatted as numeric (and don't have any rows containing string values, very important if we want the Multi-Field Formula to function properly). Provided you selected all the columns (including Dynamic or Unknown Columns) in the column selection list found in the Multi-Field Formula configuration and there are no static references to a single year in the Multi-Field Formula, the formula should function as expected.

Another option would be to transpose the data, selecting only the "items" column as Key Column (provided its name or lower case state will remain unchanged), performing any mathematical operations on the generated Value column (by utilizing the Name column containing the year information if/where necessary) and the Cross-Tab the data, Grouping By the "items" column, selecting "Name" as Change Column Header and "Value" as Values for New Columns. You can Select Sum as the method.

kauser

can you please illustrate these steps with an alteryx workflow

alexnajm

I would use a Dynamic Select - you can have a logical way with DateTime formulas to select the years you need. If it's the previous year and greater, then this should work

[Name]>=ToString(DateTimeYear(DateTimeToday())-1)

alexnajm

@kauser is your problem resolved?

NeoInfiniTech

Hi @kauser,

kauser_sample_flow.yxmd

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335