Filter Tool "IF" Formula

LAURAFP04

Hi community members

I am triying to solve with Alteryx a complex logical criteria to calculate a price for a large list of materials.

In order to assign a Final price I follow the below criteria:

1) If I find a weigthed average selling price (WASP) of this MATERIAL during the last three months sales, I assign this value. If not follow step.

2) If I find a weigthed average selling price (WASP) of this MATERIAL during the last current year to date sales, I assign this avlue. If not follow step.

3) If I find a weigthed average selling price (WASP) of this MATERIAL during the previous year sales, I assign this avlue. If not follow step.

4)If I find a weigthed average selling price (WASP) of  THE FAMILY PRODUCT during the last current year to date sales, I assign this avlue... etc...with the current year to date and the previos year sales at family product level.

Find a table with an example:

	ASIGNATION CRITERIA	COLUMN IN BLANK THAT I NEED TO ASSIGN
Material	WASP Last Quarter Current Year Material	WASP Last Quarter Current Year Family Group	WASP Current YTD Material	WASP Current YTD Family Group	WASP Previous Year Material	WASP Previous Year Family Group	Final WASP (USD)
Orange	2.75	2.75	2.75	2.75	2.63	2.63	2.75
Potato	0.00	0.00	1.80	1.80	1.55	1.55	1.80
Tomato	0.00	3.20	0.00	3.20	0.00	3.40	3.20

To make it possible I am trying a filter tool applying a custom filer:

IF [WASP Last Quarter Current Year Material] > 0 THEN 1
ELSE 0
ENDIF

Then, I take the true results and apply a Formula Tool to take the Final WASP (USD) column and make it equal to WASP Last Quarter Current Year Material:

[Final WASP (USD)]==[WASP Last Quarter Current Year Material]

But I am not getting good results, the filter is working properly but then, the equal formulas does not work and asssign a cero as value. In spite of the fact that there is a WASP Last Quarter Current Year Material amount.

Have you got any idea about which is teh source of the problem with the equal formula? Does my thinking make sense for you to apply the assignation criteria described?

I really appreciate you inputs and comments!

Many thanks in advance.

DavidSta

Hi @LAURAFP04 ,

I'm not fully sure if I understood it correctly, so let me recap.

The column you want to calculate is "Final WASP (USD)". For this you need to apply the conditional statements you have written.

To reach this goal you can write this in one formula:

IF [WASP Last Quarter Current Year Material] > 0 THEN [WASP Last Quarter Current Year Material]
ELSEIF [WASP Current YTD Material] > 0 THEN [WASP Current YTD Material]
ELSEIF [WASP Previous Year Material] > 0 THEN [WASP Previous Year Material]
ELSEIF [WASP Last Quarter Current Year Family Group] > 0 THEN [WASP Last Quarter Current Year Family Group]
// and so on ...
ELSE 0
ENDIF

See attached Workflow as an example.

I expect you did a mistake in your formula configuration. If my sample workflow is not what you are looking for, please share your workflow with us, so that we can take a look.

Best regards,

David

SampleFormula.yxmd