Formula based on a column value

DGK1981

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.

2019-06-19_13h01_45.png

Transformation

Preparation

Accepted answers

JamesMA

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.

Workflow Picture.PNG

Let me know how you get on!

Calculating Expected Sales Using Formula.yxmd

JamesMA

@jnans great spot!

I hadn't thought of that!

It makes it much more dynamic.

@DGK1981 I have attached here an improved version after the advice from @jnans

Let me know what you think

Calculating Expected Sales Using Formula v2.yxmd

All comments

danilang

Hi @DGK1981

Because of the varying columns this looks like a case for a Transpose/Crosstab solution. Can you provide some sample data?

Dan

JamesMA

Hi,

Workflow Picture.PNG

Let me know how you get on!

Calculating Expected Sales Using Formula.yxmd

JamesMA

@DGK1981

Tagging this so it's easy to find for you

jnans

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.

new columns - june 19 2019.PNG

jnans

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

DGK1981

Thanks James, this worked perfectly!

David.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703