Formula based on a column value
- 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 am trying to create a workflow that will multiply a value based on another field and corresponding year entry: I have the following data/columns (-Sales Year -Sales volume -Warranty End period -Failure rates for each year). In the example attached i want to multiply the sales volume by the corresponding failure rates based on the warranty period range. Comments in the attached file. If this can be done, it would save a huge amount of time moving :). Many thanks. David.
Solved! Go to Solution.
- Labels:
- Preparation
- Transformation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @DGK1981
Because of the varying columns this looks like a case for a Transpose/Crosstab solution. Can you provide some sample data?
Dan
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi,
This can be achieved using a formula tool as shown below. I have created a workflow with a simple and a more dynamic option that will match the warranty end year to the appropriate return rate and only calculate expected sales volumes for the appropriate year.
Let me know how you get on!
- 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
I am posting a temporary solution for you that will work. I am hoping someone will post a more dynamic solution for this as I would now also like to know a more dynamic approach to solving this. This one would at least get you started if you needed to do this on a dataset ASAP but not ideal as you would need to keep changing the parameters whenever the years and data change a bit from what you originally have listed.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@JamesMA Your Solution would work for the most part but I believe the formula would be IF [Warrant End Period] >= [Name] THEN action it. This will then calculate both all years that was wanted in the original problem. I attempted your solution with a dataset of 4 records so I could see if I could get the same results in a larger set.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks James, this worked perfectly!
David.
- 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
So very nice article
