Formula

[Lisatang]

Hi team, could you please help me with the following question? I need to write a formula in Alteryx to identify the row that has a percentage Change of  < + /-20% for current month cost less than $100. In this table below, the 1st row is the answer. However, how to write the formula in Alteryx to identify this row?

Toys	Current month	Prior month	Value change	% change
XYZ	$68.32	$67.71	$0.61	0.9%
ABC	782.33	$1156.35	-$374.02	-32.3%

[alexnajm]

Assuming your [% change] field is a string based on the percentage sign... something like this using the absolute value (ABS) should work. Make sure to also adjust the current month accordingly, but I left it assumed as a number for now to show the differences:

IF ABS(ToNumber(ReplaceChar([% change],"%")))>20 AND [Current month]<100

THEN "Change above +/- 20%"

ELSE "Change Below +/- 20%"

ENDIF

[OTrieger]

@Lisatang 

There are different ways how to do it, it is really depending on your preference
1. you can first filter out all the items that are bigger than 100 and then check % change
2. you can do use IF condition that checks the % and the amount

The only thing that you will need to ensure is that all these fields have a numeric data type otherwise the conditions will not work

[OTrieger]

And these are only 2 ways, there are more

[Deano478]

@Lisatang have a look at the attached it should be of some use to you