Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Formula based on a column value

DGK1981
7 - Meteor

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.

8 REPLIES 8
danilang
19 - Altair
19 - Altair

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
Alteryx Alumni (Retired)

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!

 

 

JamesMA
Alteryx Alumni (Retired)

@DGK1981 

 

Tagging this so it's easy to find for you :)

jnans
8 - Asteroid

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
8 - Asteroid

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

Thanks James, this worked perfectly!

 

David.

 

JamesMA
Alteryx Alumni (Retired)

@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 :)

 

 

sangram1
5 - Atom

So very nice article

Labels