Alteryx Formula

Hi Guys,

SO i have a requirement which is like i need to make a calculated field like a YTD$ column which is a sum of columns like Jan $, Feb $ etc which is in my data. The requirement is not to change the formula every month but like an external excel input which will have the data of which columns to add for YTD or Full year. Something like an excel file which has a column exact YTD $ and the values are Jan $. So we only take Jan $ for the final formula. Is that possible ?

Datasets

Accepted answers

AkimasaKajitani

Is this helpful?

sample_AK2.yxmd

All comments

Miglani

Hi Sourav,

Based on my understanding on your problem. You have an excel file(let's call it Months_file) which can have dynamic months (Jan, Feb, March .. etc) and you need to make ONLY one formula which will cater whatever months are present in this Months_file.

If my understanding is correct, you can input this file and transpose all columns and then summarize the values.
To understand better what I'm talking about I've made a sample workflow for the same.
NOTE: I tried to add another month in excel file and the same workflow worked, incorporating the additional month added.

Let me know if this solves your problem. 🙂

Months_file.xlsx

Months_YTD_Workflow.yxmd

AkimasaKajitani

Hi @SouravKayal

You can use Transpose tool and Cross tab or Summarize tool.

Sample Input :

The first step is to use the transpose tool to make the data vertical.

At this tool, If the data of the March is added, "Dynamic or Unknown Columns" option will dynamically solve.

To add the column of total, you can use CrossTab tool.

Output Sample:

Or add the row of total, you can use Summarize tool.

Output Sample:

Transpose_AK_Sample.yxmd

SouravKayal

So the problem is my main data cosnists of Jan column and the values to it. This external file will only have the months. Now my formula will use the months (column headers) from this file and the values from the main data set.

SouravKayal

YTD $	ROI
Jan $	Mar $
Feb$	April $
	May $

So this can be my input file. Now the Jan $, Feb $ columns are also in my data set that is being transformed. What I was looking is if i could use the above input and keep the same formula all the time. For example my YTD formula will be a sum of the columns that is mentioned in the above input file and FY will be a sum of both the columns mentioned above. So the formula remains static in the workflow but the excel input can change every month.

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335