Alteryx Formula
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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 ?
Solved! Go to Solution.
- Labels:
- Datasets
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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. 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
as i said i wont have the $ value in the excel just the month names
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Correct
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator