Formula Tool dealing with Dynamic columns
- 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
I have a formula tool that adds up the data in 3 columns, but each month the names on those 3 columns will change. The columns are the last 3 months of data, so for example it would be Aug/July/June. When September closes those columns will now be Sept/Aug/July. I had the formula tool adding Aug/July/June but now June doesnt exist as its rolled forward a month. How can I get the formula tool to add those three columns up each month dynamically.
As part of the Dynamic Column renaming I do have it adding the word Miss to the beginning of each of those three months. Is there a way to key in on the word "Miss" to add the columns up?
Solved! Go to Solution.
- Labels:
- Transformation
- 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
Hi @cjaneczko , you can do this by parsing the month names from the field titles, then labelling the latest 3 month columns with numbers. The formula tool is then always working with the same field names:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks @FinnCharlton and @ed_hayter, this works. Is there a way to make use of a multi field formula to sum all fields that start with 'Miss_'? I can incorporate the above solutions, but ill have to rename the columns after the 1,2,3 etc. back to what they were and I was hoping to not have to do that.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@cjaneczko You can't do that using the multi-field formula tool, but you can using transpose, filter and summarise tools. Have a look at the attached workflow.
