Alteryx Formula

[SouravKayal]

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 ?

[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. 🙂

[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:

[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.

[AkimasaKajitani]

Hi @SouravKayal 

Is this helpful?

Input : 

WF : 

[SouravKayal]

as i said i wont have the $ value in the excel just the month names

[AkimasaKajitani]

Do you indicate that you have a table with column names and another table with only data?

Column Table

Data Table?

If you have sample data format, would you please show it to response accurately?

[SouravKayal]

Correct

[AkimasaKajitani · Accepted solution]

Is this helpful?